f



Stored procedure won't use index

Hi, 

We're using SQL Server 2000.  

A very basic query on a 5 million row table would not work.  If we
hard coded the one input parameter, the query used the foreign key
index and produced an instantaneous result.  If, however, we pass the
parameter in as a parameter, the query doesn't use the index and takes
forever. E.g.

Declare @ID int
SET @ID = 17697

Select top 1 AccountID from tblAccounts where GroupID = @ID

We have fixed the problem by using an index hint to force the query to
use the index.  However, my concern is - why is the index not
automatically used when we remove the hardcoded ID and repalce it with
a parameter?  Is it a problem with the index?  We do not use index
hints as a coding standard, so will this happen to other stored
procedures in our DB?

Any advice about how and why this occurs would be much appreciated.

Trudie
0
trudie (1)
12/3/2003 11:02:16 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

2 Replies
9488 Views

Similar Articles

[PageSpeed] 26

Did you create the index after the stored procedure was created? You
can try to rebuild the SP using the with recompile option. This way
the query plan is not being cached and rebuild every time you call the
SP
0
12/4/2003 2:36:04 PM
[posted and mailed, please reply in news]

Trudie (trudie@sparkdata.co.uk) writes:
> We're using SQL Server 2000.  
> 
> A very basic query on a 5 million row table would not work.  If we
> hard coded the one input parameter, the query used the foreign key
> index and produced an instantaneous result.  If, however, we pass the
> parameter in as a parameter, the query doesn't use the index and takes
> forever. E.g.
> 
> Declare @ID int
> SET @ID = 17697
> 
> Select top 1 AccountID from tblAccounts where GroupID = @ID

This is a little unclear. In the narrative, you talk about a parameter,
but the example there is a plain variable. And, yes, that makes a 
difference.

In the snippet above, SQL Server has no idea of what value @ID has, 
so it will have to make some general assumption. If the value of
GroupID is skewed, so that 4.5 half million rows all have 0 in 
the column, using the index is a very poor idea, in case @ID is 0.

When you have a parameter to a stored procedure, SQL Server does build
the query plan from the value the parameter has the first time you
call the procedure, and that plan is then cached. So if you first call
it with some value is that is too frequent to permit an Index Seek,
you will get a table scan also when you pass values that are more
selective.

Even when you call the procedure with a good value the first time, I
suspect that the optimizer does not build a plan based on an index,
if most other values are bad for the index.

-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
12/5/2003 11:29:45 PM
Reply:

Similar Artilces:

database layouts won't print, now database won't open
using Mac OS 10.3.4, filemaker pro 7. working with a database converted from filemaker 3. Everything seemed to be fine. Created a new table and imported data into that from another Filemaker 3 database (someone else created these). Layouts were created very easilly. I go to print a layout (label) and choose ALL and it will only print one record, no matter what I put in the print dialogue box. Open other databases and they all printed fine in similar layouts. Tried to "recover' it, thinking it was somehow corrupted (the woman was having a hard time printing it. I thought it was...

Willie won't, Willie won't, won't go!
Ah yes, some 70s nostalgia tunes. ...

Files won't display, directory won't delete, drive won't format
After having a few slow-access problems, I ran a virus scan (McAfee) on my D: drive (second HDD, 15Gb). To my surprise, the scan listed a directory and files which I didn't recognise: I didn't note any full pathnames, but a typical one was something like D:\RECYCLER\S-1-5-21... Dd159.zip . I wondered if this was anything to do with the Recycle bin, despite the fact that I'd emptied it immediately before the scan. No viruses were detected, but when I ran an error check I got the message "the scan could not be completed". As a precaution I decided to ba...

ODBC doesn't support stored procedures? This can't be true
In Chip Irek's "A PRIMER ON USING DB2 WITH .NET" (www.15seconds.com), he states that "...ODBC doesn't support DB2 stored procedures. So if you are building an application heavily dependant on stored procedures, you need to eliminate ODBC as your access method and consider OleDb or the managed provider." Mr Irek is an IBM Architect with Global Services and according to the article, someone with .NET experience. Now, I'd ask Mr Irek this question myself, but my email comes back undeliverable. Does he mean this for just for .NET applications? This ca...

T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures no other procedure with the same name
T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures And there is no other procedure with the same name owned by dbo or any other users. There is no error messages also, it just completes procedure and returns result server: Microsoft SQL Server 2000 - 8.00.818 (Intel X86) Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) and I don't know how do determine Client's version. What should be done (on client site or server site )to fix this problem. Thank you (mednyk@hotmail.com) writes: > T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures > And there is no other procedure with the same name owned by dbo or any > other users. > There is no error messages also, it just completes procedure and > returns result > server: > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) > Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build > 2195: Service Pack 4) and I don't know how do determine Client's > version. You can determine the client version from Help->About in Query Analyzer. I believe that SP2 or earlier of Query Analyzer does not work with SQL Server SP3. SP3 is 8.00.760. 8.00.818 is a hotfix to SP3 with a security fix. 818 should work with 760 on the client. Unless you have the server on the local machine, it is difficult to get the debugger to work. There are so many things that have to be aligned. Personally I have given up. If you are runn...

Can't Open VB Editor and VB scrip won't run AFTER reopening database
I created a database, thought all was fine until I quit it and opened it again. The VB wouldn't run, I couldn't open the editor from design view of any form, and when I tried to open it through the menu, all I would get is a corrupted window (no scroll up button, no way to access the Project Explorer) containing just the one module I had, but I couldn't get to the Class Objects. I tried to compact and repair it, and decompile (that crashed Access). I then made a copy, set the Has Module property for all forms to No, imported everything into a new database, and wrote the code again (this time I exported all the class objects and the module before quitting in case I need them later). It was working fine until I quit it and opened it up again. Something else I tried (well I've tried all sorts of things) was to import just the tables and queries into a new database, create new forms, and copy and past the form objects on to them. I then copied and pasted the script back into the Class Objects. I thought there might have been something that was corrupted in one of the forms, and this was a way to avoid it. No joy. From much playing, I've come to the conclusion (I think) that if I import anything from this database into a new one, it will screw up the new one in the same manner. What's left to try? I'm not starting again so I'm prepared to give this up, it was just a more improved version of an already existing database I'...

join won't use index / slow query
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C9A247.C47E0260 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The fields used different character sets and collating. I also had the = same issue with my zipcodes table. Thanks for everyone's help. ----------------------------------------------------- ALTER TABLE `airports` CHANGE `apt_id` `apt_id` VARCHAR( 5 ) CHARACTER = SET utf8 COLLATE utf8_bin NOT NULL=20 ------=_NextPart_000_0006_01C9A247.C47E0260 Content-Type: text/html; charset="iso-8859-1...

DVD Won't Enter, Won't Exit
Greetings, all. Long time no see. I have an iMac G5 c. Dec. 2004. A few minutes ago, I inserted a DVD, but it seems not to have mounted in the drive. The disk is just barely too far inside the machine to reach. It's not visible from the outside at all. I've gently tried to insert a second disk, hoping to push the first one all the way in, but it won't budge. I tried restarting the computer with the eject key depressed, and heard the drive trying to eject something, but, again, the disk didn't seem to move. Any ideas, short of reaching in there with a pair of tweez...

4000T won't boot won't POST
I've had this problem for a while. Repair guys (no longer servicing Amiga's) said it was a problem with the motherboard not posting. (I believe this is a PC term) Said he got it working somehow by by-passing SCSI and using only IDE. But when returned it still did not work. Even with a new Power supply, nothing seems to power up. Even the floppy doesn't 'flick' (that funny noise it makes upon power-up) Any help appreciated. thanks Phil "Phil Bastien" <pbastien@nnby.net> ha scritto nel messaggio news:c9qtlu0na3@enews1.newsguy.com... > I've had this problem for a while. Repair guys (no longer servicing Amiga's) > said it was a problem with the motherboard not posting. (I believe this is a > PC term) Said he got it working somehow by by-passing SCSI and using only > IDE. But when returned it still did not work. Are you using an X-Calibur accelerator? It is incompatible with the A4091 and, with my configuration, it hangs just after loading its software, when it resets the Amiga. I am curious about how to bypass the SCSI controller in the A4000T... -- ___ __ / __|___ Daniele Gratteri, Italian Commodore-Amiga user... /// | / |__/ Nickname: FIAT1100D - ICQ: 53943994 Ritmo S75 __ /// | \__|__\ Home page: http://www.gratteri.tk forever! \\\/// \___| E-MAIL: daniele@gratteri.tk ...since 1990 \/// ...

Servlet can't communicate with 'Database made using XML' !
Iam making an application in which the user logs in by entering username and password through a Servlet , Usernames and passwords are already stored in an XML document on the server , the Servlet has to just parse the XML document and verify the username and password iam using 'Apache Tomcat 4.1.12' and 'JAXP api for XML' processing THE PROBLEM IS THAT WHILE RUNNING THE SERVLET IT IS THROWING EXCEPTIONS(NULL POINTER EXCEPTION) THAT IT WAS UNABLE TO PARSE THE XML FILE ! IT ALSO GIVES 'http-ERROR 500' PLEASE IF ANY ONE CAN HELP>>>>>>>>> Abhishek Agrawal wrote: > Iam making an application in which the user logs in by entering > username and password through a Servlet , > > Usernames and passwords are already stored in an XML document on the > server , the Servlet has to just parse the XML document and verify the > username and password > > iam using 'Apache Tomcat 4.1.12' and 'JAXP api for XML' processing > > THE PROBLEM IS THAT WHILE RUNNING THE SERVLET IT IS THROWING > EXCEPTIONS(NULL POINTER EXCEPTION) THAT IT WAS UNABLE TO PARSE THE XML > FILE ! > IT ALSO GIVES 'http-ERROR 500' > > PLEASE IF ANY ONE CAN HELP>>>>>>>>> Please don't use all caps. It makes you look rude. Anyway, you'll need to show us your code + xml file, otherwise noone here will be able to tell what you'r...

Printer won't print, messages won't delete in WindowsXP Pro
I'm running Windows XP Pro with a Lexmark Z22 printer. Often when I try to print, the printing notification widow pops up. As usual, I hear "Printing started," but nothing happens while the seconds are ticking away on the clock. If I try to cancel the job, then check the printing queue, the job is still there, marked "Deleting." The only way to get it to print is to restart, then it works (until the next time). The only way to delete the unsuccessful print job is to go into task manager under Processes and try to guess which process to delete. This works, when I g...

Shuffle error flashing lights! Won't sync, won't play...
Have had a Shuffle for a few weeks. Worked with Itunes just fine. About a week ago, I bought a regular G4 20GB Ipod and installed it. Also great with Itunes; no problems. Today, I tried to update the songs on the Shuffle. (The Shuffle appears in the left hand pane as one icon, the 20GB Ipod appears as another one.) The update seems to go OK but when I tried to play the songs from the Shuffle, I got no sound and only flashing red and green lights. This, I see in Ipod HELP, is an error message. In Itunes, next to the # of each song supposedly in the Shuffle, is a little grey dot. I read that this means that Itunes cannot sync with the Shuffle. It is possible that I have done something or something has happened to make the Shuffle inoperable. Or, it is possible that this was the first time I tried to update the songs on it since I installed the 20GB Ipod. Can you run a Shuffle and an Ipod off the same Itunes? If so, are there any special settings you have to take care of? I have "reset" the Shuffle (turn it on and off), upgraded its operating system to 1.1 and restored it to factory settings, using the Apple downloaded updater. I would like both the shuffle and the ipod to sync and work right from my copy of Itunes, depending on which one is attached to the USB port. But right now, the Shuffle won't sync. Ideas? Solutions? I have searched Apple Ipod HELP but can't find anything about this. Dell PC, Windows XP Home...

Stored procedure to fill listbox, using 'exec'
Hey, Coming back to a piece of work I did a while back, which used a stored procedure to populate a list box. The SP takes a single parameter, and I think this is the reasoning for using 'exec' in the row source (I assume you need this for parameters?) The problem is this only works when I access the form.. If I do it from someone else's computer they get a blank listbox. They have appropriate permissions for the stored procedure, so is the 'exec' getting in the way? Can I use a parameterised sp in a row source for a listbox without using exec? or is there a way of sorting it that the user can use the stored procedure? (there's no error messages to say there's no access) The stored procedure was created using my login, not dbo (if that makes a difference) Thanks for any help you can give! Chris Not Me <Noone.is.home@here.com> wrote in news:cimalh$lrf$1@ucsnew1.ncl.ac.uk: > Hey, > > Coming back to a piece of work I did a while back, which used a stored > procedure to populate a list box. The SP takes a single parameter, > and I think this is the reasoning for using 'exec' in the row source > (I assume you need this for parameters?) > > The problem is this only works when I access the form.. If I do it > from someone else's computer they get a blank listbox. They have > appropriate permissions for the stored procedure, so is the 'exec' > getting in the way? > > C...

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never reach the Catch and the insert I try to do there to get the error message. On both Databases the user, that is taken to execute the trigger is existent and DB-Owner of both Databases. If I go and execute the Stored Procedures manually after an insert or update to Database B everything works fine. I also already tried to check on Database B if there is an insert or update from Database A and if, to execute the Stored Procedures, with the same result, nothing and all happens anymore, neither update on Database A and also not on Database B. And also I cant catch the error as the Try/Catch is not working. Hope I could explain it understandable and maybe someone remembers already having the same problem. Thanks & Best regards Pascal (pascal.baetscher@gmail.com) writes: > I face currently a problem where I could need some input for searching > the source of the Problem > >...

Can't connect to database using 'sqlplus <username>/<password>', even when ORACLE_SID is set.
Hi, As oracle user, I can use 'sqlplus <username>/<password>' to log in to a database, when ORACLE_SID is set. But when I'm login as root, and then set the ORACLE_SID, but 'sqlplus <username>/<password>' will give me the following error: ERROR: ORA-01034: ORACLE not available I tried 'sqlplus <username>/<password>@BSCS_RTX' and I can connect. I'm absolutely confuse on why I must use the alias to connect to the database. The database version that I'm running on is 8.1.6.3 Please please help. Thank you very much in advance. Best Regards, Jeffrey Yee "Jeffrey Yee" <jeffyee@hotmail.com> wrote > Hi, > > As oracle user, I can use 'sqlplus <username>/<password>' to log in to > a database, when ORACLE_SID is set. But when I'm login as root, and > then set the ORACLE_SID, but 'sqlplus <username>/<password>' will give > me the following error: > ERROR: > ORA-01034: ORACLE not available > > I tried 'sqlplus <username>/<password>@BSCS_RTX' and I can connect. > I'm absolutely confuse on why I must use the alias to connect to the > database. The database version that I'm running on is 8.1.6.3 > You need to start your database before you are able to connect to the database Try google and you will find the solutions :-) "Jeffrey Yee" <jeffyee@hotmail.com> a �crit dans l...

Print Spooler won't start; dependent on lexbces.exe but that won't start either; So why?
Windows XP Home SP2 I have two Lexmark printers installed (or at least I used to). Actually, I have the Lexmark folders in Program Files, but the printers don't seem to be installed anymore. The printers are Lexmark 640 series Lexmark 2400 series They pretty much worked until just now. In the Printer and Faxes folder I only see the Add Printer and nothing else. When I try to start Add Printer, I get an alert saying the Printer Spool service isn't running. When I go to (run) services.msc , I find that Print Spooler is stopped. (It's stopped and) It won'...

HELP: Classic Won't Launch - Problems Have Escalated
System: MDD DP with OS 10.4.8. Three physical HD's. Two with Tiger installed (one is the major HD and the other is the emergency boot drive with just a virgin system on it) and one with just games, no OS. I tried to boot into OS 9.2.2 (this particular G4 is OS 9.2.2 bootable), It boots but once the desktop appears moving the mouse over the menu bar has no effect. Menus don't pull down and the only way to shutdown is via a hard shutdown. When I try Target Disk Mode with an iBook G4, the only drive that mounts is the games drive which has no system on it. I can't get the opitcal dri...

Visual Foxpro program to create a table archive (stores last month's data which isn't needed for this month's report), use visual basic to write??? possible? how?
Hi... I have a foxpro program which is for producing excel reports back ended to foxpro table(.dbf). Since the table is getting bigger and bigger, it slows down my program. I need a program which can create a table archive to store last month's data which isn't needed for this month's report. Therefore, I can run that archive program once a month to save outdated data into the table archive. By doing that, I can keep my current table small. Also I will have a big accumulated backup archive table to store my old data. Thanks... Kate (yenmei20878@hotmail.com) ...

How to add records with 'Autonumber' property to a MS Access database using LabVIEW?
I am trying to write to a MS Access database. Everything works fine as long as I add data to all records at the same time. The database I am using have two tables (just for testing) with a 'one-to-many' relationship between the two. They both have a PrimaryKey with the data type 'AutoNumber'. From what I understand those numbers should be automatically generated when new data is written to the DB. I can't get that to work with LabVIEW. I am using the function 'DB Tools Insert Data.vi'. According to the manual I should be OK with specifying the column I want to write...

MS Office X can't Start Becasue MS is already in use
I have MS Office X legally installed on my desktop and on my PB. However I get this irritating error message frequently and it shuts down my opened program (Entourage or Words). I went to MS's website, http://www.microsoft.com/mac/otherproducts/officex/using.aspx?pid=usingofficex&type=howto&article=/mac/LIBRARY/how_to_articles/office2004/pid_infraction.xml and it said I can legally install the program on one desktop and one laptop, however if they are on the same network, it will bring up this message. Is there anyway to defeat this? Thanks. * posted via http://mymac.ws It said...

Problem updating bios V20Z. SP went ok, bios won't go and machines won't start anymore
Hi, Got three V20Z's here, which needed a SP/BIOS update. All updated to 2.4.0.10 SP without problem One of them updated BIOS to 1.35.2.2 without problem. The other two didn't want there BIOS updated. What even makes it worse is that they won't start at all anymore! Tried all sort's of things, resetting SP, clearing CMOS, reverting SP to older versios, etc. etc. Nothing which solved it This is what it shows when I try to update localhost $ platform set os state update-bios /mnt/sw_images/platform/firmware/bios/V1.35.2.2/bios.sp This command may take several minutes. Plea...

Can I create a 'Top n' statement within a stored procedure using a parameter?
In a 'Top n' type statement I wish to be able to insert the n value from a parameter, within a stored precedure eg Having declared @pageSize as a parameter I want to run the following type of query : SELECT DISTINCT TOP @pageSize routeID, routeName FROM tblRoute_Header When I attempt to do so I get an error mesage indicating incorrect syntax. I do not get an error message if I specify 'n' directly as in TOP 10 Am I missing something or is this not possible within a stored procedure? Best wishes, John Morgan On Mon, 12 Apr 2004 16:45:26 +0100, John Morgan wrote: > > >In a 'Top n' type statement I wish to be able to insert the n value >from a parameter, within a stored precedure eg > >Having declared @pageSize as a parameter I want to run the following >type of query : > >SELECT DISTINCT TOP @pageSize routeID, routeName FROM >tblRoute_Header > >When I attempt to do so I get an error mesage indicating incorrect >syntax. I do not get an error message if I specify 'n' directly as in >TOP 10 > >Am I missing something or is this not possible within a stored >procedure? > >Best wishes, John Morgan The TOP clause will only take an integer value, not a variable. There are two other ways to limit your output to @pageSize rows: 1. Using proprietary syntax, not portable to other DBMS's SET ROWCOUNT @pageSize SELECT DISTINCT routeID, routeName FROM tblRoute_Header WHERE...

Ubuntu Lurid crapware: "extremely disappointing...won't boot...won't recover...no terminal mode... Nothing"
#1167 at http://ubuntuforums.org/showthread.php?t=1465548&page=117 Linux == guaranteed disappointment. On Tue, 06 Jul 2010 12:17:41 -0400, DFS wrote: > #1167 at > http://ubuntuforums.org/showthread.php?t=1465548&page=117 > > > Linux == guaranteed disappointment. No, it doesn't. -- Rick On 2010-07-06, the following emerged from the brain of Rick: > On Tue, 06 Jul 2010 12:17:41 -0400, DFS wrote: > >> #1167 at >> http://ubuntuforums.org/showthread.php?t=1465548&page=117 >> >> >> Linux == guaranteed disappointment. > > No, it doesn't. Indeed it doesn't. It's a very good kernel! -- BOFH excuse #337: the butane lighter causes the pincushioning On 06-07-10 18:40, Rick wrote: > On Tue, 06 Jul 2010 12:17:41 -0400, DFS wrote: > >> #1167 at >> http://ubuntuforums.org/showthread.php?t=1465548&page=117 >> >> >> Linux == guaranteed disappointment. Lie! > > No, it doesn't. Fact is that all recent GNU/Linux releases kick Microsoft Windows 7's ass in stability, usability, hardware support, (lower) resources requirement and price. Of course a lying Astroturfer like DFS can't have that and thus spreads misinformation. -- |_|0|_| Marti T. van Lin, alias ML2MST |_|_|0| Registered GNU/Linux user 513040 |0|0|0| http://www.soundclick.com/martivanlin Rick wrote: >> Linux == guaranteed disappointment. ...

Re: How to add records with 'Autonumber' property to a MS Access database using LabVIEW?
I applied the same approach as you guys have described, however my problem starts before I get to autonumber. I have an issue feeding column (data) from "DB tool list column VI" to "DB Tools insert data VI" .So when&nbsp; I connect&nbsp; column output of "DB tool list vi" to column input of "Insert data Vi " I get an error message "exception occurred in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.." However, when I don?t connect column between those two v...

Web resources about - Stored procedure won't use index - comp.databases.ms-sqlserver

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

New Video Series Shows The Reality Of Abortion Procedures
New Video Series Shows The Reality Of Abortion Procedures

Yay going in for kidney stone procedure soon!
There is a G-d i have been in the hospital with stone pain and bowel pain the past week. the bowel issue resolved by itself without surgery ...

Resources last updated: 3/22/2016 10:12:29 AM