A tale of two where clauses...

Question

A tale of two where clauses...

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Igor Raytsin Ten Centuries Points: 1341 More actions · Answer 1

Let me guess. Your prod server is SQL 7 and your dev - is SQL 2000?

Query optimizer in SQL 2000 much better that in SQL 7.

But still, even SQL 2K BOL says: "Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended."

That's why they still need DBAs - to make they query fast.

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 2

Thanks for your help Igor. My test and production servers are nearly identicle, except test has less ram. They are the same os and SQL versions. Even though the FROM joins are fast, so are the WHERE when I add the extra condition. My main problem is that these queries are getting generated from Excel pivot tables. I need to make a change on the server side so that their spreadsheets run fast again. Something has made them run drastically slower in the last two weeks. I thought by disecting the query it would point me to a change I can make in the db.

Any ideas?

Igor Raytsin Ten Centuries Points: 1341 More actions · Answer 3

It’s difficult to say why Query Optimizer do not choose most effective plan. Especially, when you are saying that indexes and statistics are OK. It seems that one extra condition for Shifts table helps Optimizer to choose faster (correct -?) execution plan. (I wonder if shiftdate>0 will have same effect). Besides, MS have some “known issues” with Query Optimizer as well.

My suggestion – take a look at OLAP cubes and build pivot tables base on them.

Also, you can use predefined query (MS Query) or stored procedures (preferred) as the Data Source of your pivot tables. Here you can say Optimizer HOW you want to get data and do not depend on Excel’s or Optimizer’s way.

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 4

Well, after 2 weeks of running on a new server with the same hardware (dual processors enabled) the performance problem has never returned. I still don't know what the problem was, must have been something in the config or a corrupt install of SQL itself. I sure hope that problem does not come back!

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 5

Hi aaron_myers

its nice to see your reply post after a long time. well this is how some problems get solves.

Every one will rack their brains and finally some how some where will get corrects itself and starts working. computer is really a uncertain thing. You do test of your program 100 times and it will work perfectly. But in front of your Project manager or the Client on a demo it will crash.

But each time we learn a lot through this types of bugs which is not their in the helps or manuals. Isnt it ?

--Jeswanth

--------------------------------

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 6

It always annoys me when I see a newsgroup thread somewhere about a problem I am having, and the conversation stops before the problem is found or the resolution is posted. I will say that I recieved much better support and ideas from this forum than the MSDN Managed discussion groups.

Thanks for trying everyone!

Aaron Myers Ten Centuries Points: 1139 More actions · Answer 7

To all:

Thank you for your time and ideas. After Microsoft analyzed the issue and reproduced it on their end, this behavior was deemed a bug. The temporary work around is to disable Table Spooling using trace flag 8690. I'll let you know if there are any further developments.

-Aaron

Igor Raytsin Ten Centuries Points: 1341 More actions · Answer 8

Thanks for the update, Aaron.

It's useful to know about another undocumented trace flag.

Igor