I've got a pretty basic SQL query that's become a bottleneck in my processing. It's selecting a large varchar(999) column that's slowing it down. Removing that column from the select speeds it up considerably so I know it's the column that's causing problem.
I was experimenting with breaking it up into smaller 300 record batches to see if that helped and I saw something weird happening. Some of the batches were taking almost 30 seconds, and some were taking 0.012 seconds. I don't know what's causing this discrepancy.
I have a reproducible scenario where the first query is taking many times faster than the 2nd:
select r.ID, r.FileID, r.Data
from Calls c
join RawData r on r.ID = c.ID
join DataFiles f on f.ID = r.FileID
where r.ID between 1118482415 and 1118509835
0.3 seconds
select r.ID, r.FileID, r.Data
from Calls c
join RawData r on r.ID = c.ID
join DataFiles f on f.ID = r.FileID
where r.ID between 1115330220 and 1118482415
8 seconds
I see no visible differences in the returned data. They both return 300 records and all of the record's "Data" column values are about 170 characters long. I'm running this directly from the SqlStudio client. Also there's no other traffic in this database.
Does anybody know what could be causing this problem or have any suggestions to try? I can't decrease the size of the column because there are some bigger records in there, just not in this example. I do have indexes on all the columns used in the joins (Calls.ID, RawData.ID, RawData.FileID, DataFiles.ID).
Aucun commentaire:
Enregistrer un commentaire