S
schick
I have a C# console application that uses OleDbConnection, OleDbDataReader,
and OleDbCommand to directly read from a local Access database through the
"Microsoft.Jet.OLEDB.4.0" provider. (WinXP Pro w/ 512mb RAM and .Net 1.1
system)
The first time the app executes the SELECT statement show below it executes
and prints 837 records in less than 2 seconds. CPU usage stays below 25%.
When I run the same query again the app stalls a few times reading the
results. By the fourth or fifth run it takes 25 seconds to finish and often
consumes 99% of the CPU.
Looking at the stack, the app stalls in the
System.Data.OleDb.OleDbDataReader.GetRowHandles() method (which is called
indirectly from the OleDbConnection.Read() method). The stalls always occur
calling Read for the same records, and they don't continue to get worse
after about five runs. Between the stalled reads, the records come in fast
and CPU usage goes down.
All tables have indexes where you would expect and nothing else is accessing
the database when I run these tests. The app has finally blocks to Close()
all connections and readers. I can run the same SELECT query in Access 2003
dozens of times with continually fast performance. I tried to "Compact and
Repair" the database from Access, but that did not help.
This is the stalling query:
SELECT it.fImageOriginalFileName, it.fImageCaption, it.fMediaFullPath,
it.fMediaProxyPathIdFromMedia, it.fImageOriginalFilePath
FROM ImageTable AS it
WHERE EXISTS (Select if.fImageId FROM xImageFolders if WHERE
if.fImageId=it.fImageId AND if.fFolderId=32)
OR
EXISTS (Select if.fImageId FROM xImageFolders if WHERE
if.fImageId=it.fImageId AND if.fFolderId=306);
The OR between the sub-selects seems to trigger the stalls because the
following query returns 823 of the same records and never stalls.
SELECT it.fImageOriginalFileName, it.fImageCaption, it.fMediaFullPath,
it.fMediaProxyPathIdFromMedia, it.fImageOriginalFilePath
FROM ImageTable AS it
WHERE EXISTS (Select if.fImageId FROM xImageFolders if WHERE
if.fImageId=it.fImageId AND if.fFolderId=306);
and OleDbCommand to directly read from a local Access database through the
"Microsoft.Jet.OLEDB.4.0" provider. (WinXP Pro w/ 512mb RAM and .Net 1.1
system)
The first time the app executes the SELECT statement show below it executes
and prints 837 records in less than 2 seconds. CPU usage stays below 25%.
When I run the same query again the app stalls a few times reading the
results. By the fourth or fifth run it takes 25 seconds to finish and often
consumes 99% of the CPU.
Looking at the stack, the app stalls in the
System.Data.OleDb.OleDbDataReader.GetRowHandles() method (which is called
indirectly from the OleDbConnection.Read() method). The stalls always occur
calling Read for the same records, and they don't continue to get worse
after about five runs. Between the stalled reads, the records come in fast
and CPU usage goes down.
All tables have indexes where you would expect and nothing else is accessing
the database when I run these tests. The app has finally blocks to Close()
all connections and readers. I can run the same SELECT query in Access 2003
dozens of times with continually fast performance. I tried to "Compact and
Repair" the database from Access, but that did not help.
This is the stalling query:
SELECT it.fImageOriginalFileName, it.fImageCaption, it.fMediaFullPath,
it.fMediaProxyPathIdFromMedia, it.fImageOriginalFilePath
FROM ImageTable AS it
WHERE EXISTS (Select if.fImageId FROM xImageFolders if WHERE
if.fImageId=it.fImageId AND if.fFolderId=32)
OR
EXISTS (Select if.fImageId FROM xImageFolders if WHERE
if.fImageId=it.fImageId AND if.fFolderId=306);
The OR between the sub-selects seems to trigger the stalls because the
following query returns 823 of the same records and never stalls.
SELECT it.fImageOriginalFileName, it.fImageCaption, it.fMediaFullPath,
it.fMediaProxyPathIdFromMedia, it.fImageOriginalFilePath
FROM ImageTable AS it
WHERE EXISTS (Select if.fImageId FROM xImageFolders if WHERE
if.fImageId=it.fImageId AND if.fFolderId=306);