python调用sp,一秒钟最多调用40次。 用c#代码调用,一秒钟能调用450次相同的SP
为什么差这么多。
python 是采用 adodbapi.python python 操作MS SQL Server 2005 效率到底怎么样啊。
为什么差这么多。
python 是采用 adodbapi.python python 操作MS SQL Server 2005 效率到底怎么样啊。
关于效率的问题,如果是相同的数据库,例如用C#代码和java代码调用后效率不同,那就是程序的问题,和数据库无关。
class GppDBApi:
'''
============================================================================
additional feature to adodbapi:
Access Values By Name : Values can be accessed by column name.
records are Iterable
Connection Pooling
retry when connect timeout
retry when call proc failed
make adodbapi looks like pyodbc:
http://code.google.com/p/pyodbc/wiki/Features
============================================================================
Note:Please write db code according to Python dbapi 2.0
This is the database api for all PyGPP project.
As we are using Microsoft SQL Server for now, we have to use
1.adodbapi(Windows only) 2.pymssql 3. pyodbc(can't support SP output param)
If we need to deploy on Linux, we have to use pymssql or pyodbc,
but for now they are not mature and have many bugs and limits,
finally we choose to use adodbapi as it's mature and stable, however,
we need to deploy on Windows server.
'''
@logEntryExit
def __init__(self, version, DataCfg=None):
if not DataCfg:
DataCfg = CDataCfg(version)
self.datacfg = DataCfg
self.connectionPool = {}
def __del__(self):
for conn in self.connectionPool.itervalues():
conn.close()
'@retryWhenExcept(3, delay=30, backoff=1)'
'@logEntryExit'
def connectWithRetry(self, connstr):
return adodbapi.connect(connstr, 60 * 20) '@retryWhenExcept(3, delay=30, backoff=1)'
'@logEntryExit'
def callProcWithRetry(self, cursor, SPName, inputParams):
return cursor.callproc(SPName, inputParams)
'@logEntryExit'
def connectDB(self, connstrName):
connstr = self.datacfg.DBcfg[connstrName]
#analyze from SQL conn str:
#Server=SZGFDEVPerfDB11;Database=RawData;uid=DMClient;
#pwd=2Fetch(Get;Application Name=Performance;
connStrDict = splitToDict(connstr, ';', '=')
connstr = 'Provider=SQLOLEDB.1; Initial Catalog={0[Database]}; '\
'Data Source={0[Server]}; user ID={0[uid]}; '\
'Password={0[pwd]};'.format(connStrDict)
conn = self.connectWithRetry(connstr)
return conn '@logEntryExit'
def getConnection(self, connstrName):
if connstrName not in self.connectionPool:
self.connectionPool[connstrName] = self.connectDB(connstrName)
return self.connectionPool[connstrName] '@logEntry'
def getRecords(self, cursor):
#fetchone : An Error (or subclass) exception
#is raised if the previous call to executeXXX()
#did not produce any result set or no call was issued yet.
try:
row = cursor.fetchone()
except Exception, e:
#logSys.debug('no rows produced {0} {1}'.format(Exception, e))
return None records = CRecords()
records.description = cursor.description
while row:
record = CRecord()
dumpstr = u''
for colID, fieldName in enumerate(cursor.description):
setattr(record, str(fieldName[0]), row[colID])
try:
dumpstr = u'{0}\t{1}'.format(dumpstr,
str(row[colID]).encode('utf-8'))
except Exception, e:
logSug.debug('exception: {0} {1};'
.format(Exception, e))
logSug.debug('value for {0}\n record: {1}'
.format(fieldName[0], dumpstr))
dumpstr = u'{0}\t{1}'.format(dumpstr,'Invalid')
#setattr(record, str(fieldName[0]), 'Invalid')
setattr(record, 'dump', dumpstr.strip())
records.rows.append(record)
row = cursor.fetchone()
return records '@logEntry'
def callSP(self, connstrName, SPName, * inputParams):
'''
@param inputParams:
Only need input params, please don't provide outputParams
@return: a tuple of (records, outputParams)
'''
'beginTime = datetime.now()'
currentConn = self.getConnection(connstrName)
cursor = currentConn.cursor()
try:
retValues = self.callProcWithRetry(cursor, SPName, inputParams)
except Exception, e:
logSys.critical('callproc exception: {0} {1}'.format(Exception, e))
timeElapsed = datetime.now() - beginTime
logSys.info(SPFailedInfo.format(connstrName, SPName,
inputParams, timeElapsed))
raise Exception, e #retvalues is a full list of params, and we only needs output
outputParams = retValues[len(inputParams):]
records = self.getRecords(cursor)
cursor.close()
#Update action must commit, otherwise db will be locked
#If it's a query SP, this will be an empty commit, which has no impact
currentConn.commit()
'timeElapsed = datetime.now() - beginTime' '''if records:
logSys.info(SPSucceedInfo.format(connstrName, SPName,
inputParams, outputParams,
len(records.rows), timeElapsed))
else:
logSys.info(SPSucceedInfoParmasOnly.format(connstrName, SPName,
inputParams, outputParams,
timeElapsed))'''
return records, outputParams
'@logEntry'
def callSPSound(self, connstrName, SPName, * inputParams):
'''
@param inputParams:
Only need input params, please don't provide outputParams
@return: a tuple of (records, outputParams)
'''
'beginTime = datetime.now()'
currentConn = self.getConnection(connstrName)
cursor = currentConn.cursor()
try:
retValues = self.callProcWithRetry(cursor, SPName, inputParams)
except Exception, e:
logSys.critical('callproc exception: {0} {1}'.format(Exception, e))
timeElapsed = datetime.now() - beginTime
logSys.info(SPFailedInfo.format(connstrName, SPName,
inputParams, timeElapsed))
raise Exception, e #retvalues is a full list of params, and we only needs output
outputParams = retValues[len(inputParams):]
'records = self.getRecords(cursor)'
cursor.close()
#Update action must commit, otherwise db will be locked
#If it's a query SP, this will be an empty commit, which has no impact
currentConn.commit()
'timeElapsed = datetime.now() - beginTime' '''if records:
logSys.info(SPSucceedInfo.format(connstrName, SPName,
inputParams, outputParams,
len(records.rows), timeElapsed))
else:
logSys.info(SPSucceedInfoParmasOnly.format(connstrName, SPName,
inputParams, outputParams,
timeElapsed))'''
return retValues, outputParams#===============================================================================
if __name__ == '__main__':
from pprint import pprint
testdb = GppDBApi('Beta') records, _ = testdb.callSP('SupportDataClientConnStr',
"getAllValidFrequencyPatterns")
另外看看你的代码上是不是有些问题.呵呵,我也正在学python,觉得这个语言挺好玩的。以后多交流交流.
为什么呢?