January 17, 2024 at 12:22 pm
folks
i have a relatively large table (22mil records) to the rest of our sql tables
using the application, i can query all transactions for a value B700 that has 9799 records in year 2023 in 2-3 seconds
using the same application, i query C900 in year 2023 and it takes minutes to return data - with only 288 records
they both use the same clustered index to return the results (0.01% fragmentation)
the only thing i can see different here is that B700 is always upper case in the table, but for some reason C900 is mixed case
is that a red herring? or anyone have any ideas for me
cheers
mal
January 17, 2024 at 1:18 pm
To be sure, I'd need to see the query and an execution plan. Also, the collation you're using could affect this. However, in general, no, that shouldn't be a concern. Again, depending on the settings on your system, by default, searching for 'C900' or 'c900' is exactly the same. The difference is if you decide to "help" and do something like UPPER(Mycolumninquestion) = 'C900' or other similar things. Have you looked at the execution plan for both queries? Is it the same? 100% the same? That's going to give you a lot more information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2024 at 1:19 pm
To be sure, I'd need to see the query and an execution plan. Also, the collation you're using could affect this. However, in general, no, that shouldn't be a concern. Again, depending on the settings on your system, by default, searching for 'C900' or 'c900' is exactly the same. The difference is if you decide to "help" and do something like UPPER(Mycolumninquestion) = 'C900' or other similar things. Have you looked at the execution plan for both queries? Is it the same? 100% the same? That's going to give you a lot more information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2024 at 2:31 pm
To add to what Grant said, are you using this in a stored procedure and passing that B700 and C900 as parameters? If so, then this could be a parameter sniffing problem.
Alternately, any chance that B700 and C900 are different columns? If they are different columns, it could very well be that the clustered index is good for the column that contains B700 but is useless for the column C900.
Another thing, are those numbers consistent? What I mean is does it ALWAYS come back quickly with B700 and ALWAYS come back slowly with C900? The reason I ask is table usage (INSERT, UPDATE, DELETE) can cause blocking on the table and MAYBE when you are running the query for C900, there are a lot of CRUD operations happening but when you are running it with B700 there isn't much.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 17, 2024 at 2:52 pm
HI FOLKS
ill go back and check the index's being used on both to ensure both the same (its the exact same query using exact same front end application) only the parameter is different
they are being queried from front end ERP system, both definitely are using the same field (it a finance GL code)
and the fast transaction is fast 100% of the time, the slow transaction is slow 100% of the time
cheers
mal
January 17, 2024 at 3:05 pm
Another question for you then - is it consistently FAST and SLOW if you run the same query in SSMS and the ERP OR is it only fast/slow in the ERP? The reason I ask that is that it COULD be that there is nothing wrong on the SQL side, but that the ERP is misbehaving for some reason with the result set.
Any chance that the row size is larger with C900 vs B700? Like if there are any LOB columns, could it be that B700 pulls back no BLOBs and only has a few KB of data but C900 has more BLOBs and is pulling back MB or even GB of data even with fewer rows?
Mostly, I am curious if you are comparing apples to apples.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 17, 2024 at 8:16 pm
folks
I can query all transactions for a value B700 that has 9799 records in year 2023 in 2-3 seconds
using the same application, i query C900 in year 2023 and it takes minutes to return data - with only 288 records
So, you are specifying the year in the query conditions (WHERE) as well? Need to know what specific index(es) are on the table, at least, although full DDL would naturally be even better.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 17, 2024 at 9:00 pm
HI FOLKS
ill go back and check the index's being used on both to ensure both the same (its the exact same query using exact same front end application) only the parameter is different
they are being queried from front end ERP system, both definitely are using the same field (it a finance GL code)
and the fast transaction is fast 100% of the time, the slow transaction is slow 100% of the time
cheers
mal
Oh, in that case, it could be bad parameter sniffing. For testing purposes, run the query with a recompile (either use a hint, or use DBCC FREEPROCCACHE passing in the plan handle, please, passing in the plan handle so it only removes one plan from cache). Run it for both values. For my money, you'll get two plans. The issue is that the plan that works well for Dwhatever doesn't work as well for Cwhatever.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2024 at 5:30 am
Not enough info... we're just guessing from descriptions. Please see the article at the 2nd link in my signature line below for what to post for these types of performance issues.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply