Stored Procedure takes a stupidly long time

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steven Scaife (spam)
Date: 08/03/04 

Date: Tue, 3 Aug 2004 18:28:45 +0100

Hi, I have just started a new job and my first task is to test the viability
of placing the database, currently 80 gig, and holding 3.5 million customers
onto the intranet. The current system that they use to update search etc
the details, is via a C++ front end and a typical search for example on
mcdonalds takes about 4 secs.

In my stored procedure I am looking at just short of 2 mins to get the same
data. I am using a like statement with the % wildcard before and after the
search criteria. I know this is pretty crappy but it appears that the old
system uses a similar searching method. If i remove the beginning % then it
is an instance search, but it needs to match mcdonalds anywhere in the
company name. eg it could be edward mcdonalds & son but all they remember is
the mcdonalds bit. This is the first time i have used SQL server, I am a
recent graduate, but have experience with ASP for the last two years using
Access in a business environment, which is why they set me on, to work
wonders with ASP (hopefully lol). As I understand a stored procedure is a
compiled SQL statement that does a lot more, ie loops and stuff. So this is
the first time i created one. Is there a better way of performing the
search I need cutting down the search time. to a reasonable amount. I
thought of IN but thats not what is required. Also i may not have created
the stored procedure right. I created in the computer management snap in
where you can view tables, stored procedures in a window. do i have to do
something to compile it or is it already compiled.

thanks for your time

Relevant Pages

  • Re: Stored Procedure slow when called from Crystal Reports
    ... Access executes ... that statement to my stored procedure that is executed by the report, ... report runs about 3 times faster - almost as fast as the SP on its own. ... Yes, it is slow the first time, but that's before ...
    (microsoft.public.vb.crystal)
  • Re: Clearing a dataset
    ... > parameterised stored procedure from MS Access. ... > comes up with the same result as the first time. ... > Im selecting two things from cbo boxes, and wanting the result to be ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Clearing a dataset
    ... > Im a sort of newbie when it comes to VB.NET, so forgive me if this is ... > parameterised stored procedure from MS Access. ... > comes up with the same result as the first time. ... > Im selecting two things from cbo boxes, and wanting the result to be ...
    (microsoft.public.dotnet.languages.vb)
  • Re: question on stored procedure and different parameters
    ... When you run the stored procedure for the first time, ... (in the first SELECT ProductNumber, in the second SELECT LoginID), then ... the optimizer will always generate a query plan that uses an index seek. ...
    (microsoft.public.sqlserver.programming)
  • Clearing a dataset
    ... parameterised stored procedure from MS Access. ... it, but when i go and try and run the stored procedure again, it ... comes up with the same result as the first time. ... Im selecting two things from cbo boxes, and wanting the result to be ...
    (microsoft.public.dotnet.languages.vb)