this is a split from thread http://forums.devart.com/viewtopic.php? ... ow#p112101. Queries against a MSSQL Server are 5-6 times slower than their ODBC counterparts. I´ve created a simple project that runs queries returning ~2100 records from various tables. For each query there are two versions: The 'local' version creates a new db connection and runs the query, the 'existing' version uses an existing db connection and runs the query. I´ve run each query twice, please see the attached log.
Code: Select all
SQL Query:
"SELECT "
"rundefects.segmentID, "
"rundefects.volume, "
"rundefects.lastupdate, "
"rundefects.box_left, "
"rundefects.box_top, "
"rundefects.box_right, "
"rundefects.box_bottom,"
"rundefects.LocationMeters, "
"joblanes.fromm, "
"reporttext.text "
"FROM "
"rundefects "
"LEFT JOIN runsegments ON "
"runsegments.jobkey = rundefects.jobkey AND "
"runsegments.runkey = rundefects.runkey AND "
"runsegments.rollid = rundefects.rollid AND "
"runsegments.segmentid = rundefects.segmentid "
"LEFT JOIN joblanes ON "
"runsegments.lane = joblanes.laneID AND "
"runsegments.jobkey = joblanes.jobkey "
"LEFT JOIN reportdefects ON "
"reportdefects.defecttype= rundefects.type "
"LEFT JOIN reporttext ON "
"reporttext.code = reportdefects.reporttext_name "
"WHERE "
"rundefects.jobkey = :jobkey AND "
"rundefects.runkey = :runkey AND "
"rundefects.rollid = :rollid "
"ORDER BY "
"rundefects.locationmeters ASC";
I can provide the project used to create these outputs and the MSSQL database as well.Machine:
Intel i7-3820 @ 3.60GHz
16 GB RAM
Windows 7 Professional 64bit Service Pack 1
Project:
Codegear RAD Studio 2007
32bit Release Mode
Optimized for speed
Executing UniDAC query with local db connection
Query returned 2133 records in 0,704185469032532 seconds.
Executing UniDAC query with local db connection
Query returned 2133 records in 0,679717437024836 seconds.
-----------------------------------------------------------------------------------------------------
Executing UniDAC query with existing db connection
Query returned 2133 records in 0,728303828082283 seconds.
Executing UniDAC query with existing db connection
Query returned 2133 records in 0,731041656740833 seconds.
-----------------------------------------------------------------------------------------------------
Executing ODBC query with local ODBC datasource
Query returned 2133 records in 0,159443778940896 seconds.
Executing ODBC query with local ODBC datasource
Query returned 2133 records in 0,0856968162461516 seconds.
-----------------------------------------------------------------------------------------------------
Executing ODBC query with existing ODBC datasource
Query returned 2133 records in 0,109581776395345 seconds.
Executing ODBC query with existing ODBC datasource
Query returned 2133 records in 0,100410589220204 seconds.
-----------------------------------------------------------------------------------------------------
Regards,
Guido Niewerth