Query Performance

From: hdsjunk (anonymous_at_discussions.microsoft.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 06:13:09 -0700

Good Morning All!

I have a couple questions regarding query performance:

#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?

#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.

Any help would be greatly appreciated, as I am new to
working with indexes.

Thank you,
Heidi

Relevant Pages

  • Re: CREATE VIEW error
    ... exists, yes the query works in access development, asking more is time ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... The idea is that i need to write me complex queries, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CREATE VIEW error
    ... exists, yes the query works in access development, asking more is time ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... The idea is that i need to write me complex queries, ...
    (microsoft.public.dotnet.framework.adonet)
  • SQL Injection
    ... I am currently pen-testing a web app and I am stuck in trying to execute two queries sequentially in Oracle. ... To my knowledge I can do this in SQL by separating the two queries with; however this is no happening in my case in two circumstances... ... I have a discovered an injectable sql query that is fed its data from a web form, the end query build by a cgi-script being ... This list is provided by the SecurityFocus Security Intelligence Alert ...
    (Pen-Test)
  • Re: Query from a list
    ... It works great for action queries but won't execute a "Select ... Cannot execute a select query. ... Set qry = CurrentDb.QueryDefs) ...
    (microsoft.public.access.gettingstarted)
  • Re: Strange problem?
    ... > I'm not sure if it's related with your problem but i had similar problem before, same query is working great when i execute it on my qa but when the application executes it,it timeouts. ... >>on the server looked normal (memory, services, cpu, connections, queries). ...
    (microsoft.public.sqlserver.server)
Loading