SQL - Totally Off topic

Stuff not related to religion or atheism.
Post Reply
nozzferrahhtoo
Atheist Ireland Member
Atheist Ireland Member
Posts: 1140
Joined: Thu May 29, 2008 8:17 am

SQL - Totally Off topic

Post by nozzferrahhtoo » Wed Dec 09, 2009 4:57 pm

In my usual joy of finding off topic things to post in the off topic forum, I am wondering if our ranks have any SQL experts, specifically about a query I am trying to write on SQL 2005. There is just a naggy little thing I can not do and cant find a work around for. Nor can I find an answer on google.
Gar
Posts: 187
Joined: Thu Jun 21, 2007 4:32 pm

Re: SQL - Totally Off topic

Post by Gar » Wed Dec 09, 2009 5:56 pm

I know a a bit about sql, not lots now or anything but if you post what you're trying to do I might know it.. No promises though !
nozzferrahhtoo
Atheist Ireland Member
Atheist Ireland Member
Posts: 1140
Joined: Thu May 29, 2008 8:17 am

Re: SQL - Totally Off topic

Post by nozzferrahhtoo » Wed Dec 09, 2009 6:16 pm

I have a query which ends in:

WITH INDEX....

It works beautifully.

However this query I am trying to put into an SQL view and it appears it is not letting me and I need a work around because without it things are hell.

Basically my question is, is there any way to use WITH INDEX or OPTIMISE FOR in the SQL statement in a view.
JH
Atheist Ireland Member
Atheist Ireland Member
Posts: 241
Joined: Sun Dec 02, 2007 11:43 pm
Location: Dublin

Re: SQL - Totally Off topic

Post by JH » Wed Dec 09, 2009 8:19 pm

The SQL query optimiser should look after that for you automatically. The fact you're specifying hints in your query would indicate to me that there's an index or two missing at the table level.

Have you tried executing the query in the Database Engine Tuning Advisor? That'll tell you where the bottleneck is.

PM me if you want any help with this. (It's hard to tell without seeing your database structure)
nozzferrahhtoo
Atheist Ireland Member
Atheist Ireland Member
Posts: 1140
Joined: Thu May 29, 2008 8:17 am

Re: SQL - Totally Off topic

Post by nozzferrahhtoo » Thu Dec 10, 2009 9:02 am

Yes I have tried all of that. In my test system the optimiser uses the index I want. In the live system it chooses anothers one. No matter what caches I clear, statistics I update or anything. It just seems that in the other database SQL wants to use the wrong index.

Two simple words WITH INDEX would solve all my issues instantly. Alas you can do this in any Select statement, except one in a "view" it seems.
JH
Atheist Ireland Member
Atheist Ireland Member
Posts: 241
Joined: Sun Dec 02, 2007 11:43 pm
Location: Dublin

Re: SQL - Totally Off topic

Post by JH » Thu Dec 10, 2009 10:31 pm

Is it exactly the same version of SQL Server on both systems? - I had an issue a couple of months ago where queries would run beautifully on my local SQL Server 2005, but when I uploaded to them to the production SQL Server 2000 it was a disaster because the optimiser works differently (especially when doing anything with dates)
nozzferrahhtoo
Atheist Ireland Member
Atheist Ireland Member
Posts: 1140
Joined: Thu May 29, 2008 8:17 am

Re: SQL - Totally Off topic

Post by nozzferrahhtoo » Fri Dec 11, 2009 10:03 am

Fraid so. Even the data is almost identical, as it is backed up periodically.

Ah no matter, I will find another way around it. My actual question about using With INDEX in a view seems to be unanswerable wherever I look. I guess it can not be done.
bipedalhumanoid
Posts: 2675
Joined: Fri Feb 09, 2007 1:55 pm

Re: SQL - Totally Off topic

Post by bipedalhumanoid » Tue Jan 19, 2010 4:21 pm

I wasn't able to test this in 2005 but it works fine for me in 2008. Even without schemabinding.
"The fact of your own existence is the most astonishing fact you will ever have to face. Don’t you ever get used to it." - Richard Dawkins... being shrill and offensive again I suppose.
Post Reply