November 15, 2010 at 12:12 am
Hi,
I am using sql 2000,win 2k3 on IBM SBS.
i have about 300 users who uses continuously on my software.
I have about 1300000 (500000 and 800000)
records in 2 tables which are inter linked master and trans.
so, my sql server became very slow.
till now every thing is ok but slow.
due to speed problem i put indexes on both tables
after indexing my speed is very good. AND PROBLEM CAME HERE
RESULT OF OLD QUERY IS DIFFERENT EVERY TIME I FIRE IT.
AND WHEN I REMOVED INDEXING THEN RESULT IS PERFECT.
WHAT IS THIS I CAN'T UNDERSTAND ?
here my query
select a.8,b.aasdfddd
from aaaa a, bbbbb b
where a.srno = b.srno and isnull(status,'Y')='Y'
and UPPER(isnull(b.Completed,'No'))='NO'
and b.fid in (select max(fid) from bbbb where srno = a.srno)
and td ='aaaa'
and b.actm between '1-Oct-2010 00:00' and '1-Nov-2010 23:59'
and b.eby in ('xxxx')
order by actm
IF I REMOVE SUBQUERY THEN RESULT IS PERFECT IN INDEXING TOO.
HELP 🙁
November 15, 2010 at 10:18 am
Hey,
Couple of questions.
This requirement for 'UPPER' implies case sensitivity on your server:
and UPPER(isnull(b.Completed,'No'))='NO'
so, since the 'only' thing that changed was your re-indexing, did you modify case sensitivity as well at that time in some way?
And this:
and b.fid in (select max(fid) from bbbb where srno = a.srno)
...has two possible failure components. First is the table name, second is the where equality (back to the case sensitivity issue). The max shouldn't change because t/T would sort the same way insensitive or not. b.fid IN might be different if they are varchar, and not f_id which is how I'm reading it as an integer.
Past that we'd probably need to see the feeder components of this proc and evaulate some of the internal variables/parameters you're passing, then ask some very specific questions about what exactly your data looks like for the query going correct and wrong, and what specific index build is making the difference.
The moving parts here, KCV, seem like they shouldn't affect it, so we're going to have to start looking under rocks. That's going to require time and detail on your part, just FYI.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 11:42 pm
Thanks man, for helping me
in answer to your questions :
1. I did not changed anything except indexing on a table. (query,table,data,fields all are as it is)
2. b.fid field is numeric type filed.
in simple words : my query result is getting changed every time after i put index on my table.
My query result should be 40 rows, but immediately after putting index on table it changes every time i fire query something like 85,93,104,67,45... (no data changed,no query changed nothing is changed).
if i remove sub query from main query then result is OK (even index is not removed).
i am ready to do whatever you say. i am fully authorized person in my company.
feel free to ask me anything
Pls help.
November 16, 2010 at 7:27 am
1) ALWAYS use aliases for your tables AND columns (quite a few columns are not aliased)! Try this:
select a.8,b.aasdfddd
from aaaa a, bbbbb b
where a.srno = b.srno and isnull(status,'Y')='Y'
and UPPER(isnull(b.Completed,'No'))='NO'
and b.fid = (select max(fid) from bbbb b2 where b2.srno = a.srno) --here is change. also note = instead of IN since MAX can only return ONE row
and td ='aaaa'
and b.actm between '1-Oct-2010 00:00' and '1-Nov-2010 23:59'
and b.eby in ('xxxx')
order by actm
2) stop using non-ansi joins. They are deprecated and will no longer be supported at some point in the future.
3) This is a very inefficient query structure since you have columns wrapped in functions (the isnulls), which will void an index seek if one is appropriate. It also prevents the optimizer from getting accurate statistics for the query which can lead to horrible performance problems due to suboptimal joins.
4) Consider getting a performance professional to give your app and system a quick review. Given that you have the code above and more importantly had tables with no helpful indexes I imagine there are a huge number of things that will be found and you can learn from the engagement. BIG win!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 16, 2010 at 2:08 pm
KcV (11/15/2010)
Thanks man, for helping mein answer to your questions :
1. I did not changed anything except indexing on a table. (query,table,data,fields all are as it is)
Is there anyone else who also has DBA access? Does removing the indexing remove the problem? I want to make sure we've got the issue nailed down exactly, and not having two/three people's simultaneous changes lead us in the wrong direction.
You mentioned in the first post if you remove the index the subquery works correctly. Can you run just the subquery with and without the index and see if the results differ? Specifically: select srno, max(fid) from bbbb group by srno. If it's not that, then it's the srno = a.srno component.
My query result should be 40 rows, but immediately after putting index on table it changes every time i fire query something like 85,93,104,67,45... (no data changed,no query changed nothing is changed).
That is just short of insane. I assume this procedure/query has parameters that are added to it. The reason I ask is "and b.fid in (select max(fid) from bbbb where srno = a.srno)" shouldn't have much mobility on its own.
The only mobility implied here is this portion:
and td ='aaaa'
and b.actm between '1-Oct-2010 00:00' and '1-Nov-2010 23:59'
and b.eby in ('xxxx')
I don't know what's hardcoded, and what's a parameter, there, though.
i am ready to do whatever you say. i am fully authorized person in my company.
feel free to ask me anything
So, in light of the above, let's do a few things.
First do the test above. Make sure your max() with index is the same as MAX() without.
After that, we'll need to see the DDL (the data definition) of the bbbb table. Also, all existing indexes on said table, as well as the one you're trying to include. We'll need to see the *full* procedure/query you're running. I don't care if you want to hack out some table/column names (just make sure they're consistent), but we need to see it end to end to see the exact process.
Add to that, I'd like to see the .sqlplans for the two queries, the functional and the non. You can grab instructions to get those following the link in my sig for 'index/tuning help'.
We're looking for a needle in a haystack. In the meanwhile, run a DBCC CHECKDB during a maintenance window and see if you get anything strange.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2010 at 2:31 pm
Edit: duplicate.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply