query performance with large tables

Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?
If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>

query performance with large tables

Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexe
s
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>sql

query performance with large tables

Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>

query parameter

I have to create a stored procedure where the criteria is: "All", specific value and, all except one value

so far this is what I have:

@.Status varchar (50) -- as parameter from a dropdown box

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status ='All'AND @.Status <>'All but closed'THEN'%'WHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.StatusENDand in the storedprocedure.......WHERE Statuslike @.NewStatus

I am a little confused as to how could I return all values except those that have the value "Closed"

Thanks.

in your where clause just add

AND status <> 'closed' or if you had multiple status' status: not in (x, y, z) if that is not helpful, give me a little more info and i will try and help you some more...--jp

|||

Finally I came up with this solution:

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.Statuselse'%'ENDDECLARE @.NewStatusNotvarchar(50)SET @.NewStatusNot =CASE @.StatusWHEN'All but closed'THEN'Closed'else'zz'END.........WHEREStatus.Statuslike @.NewStatusANDStatus.Statusnot like @.NewStatusNot

Not sure if this the best solution but it works

It is the same idea that you have suggested.

Query not using the right index

Hi,
Since a day, Queries that run on a specific
table "Transit" are not using the right index.
I need to add "with (index=X2,readuncommitted)" to the
query to ensure it uses the right one.
The first thing i should do is to REINDEX the table.
BUT, is there something else (less long) i could do
instead of REINDEX ?
Thanks,
DonHi,
Verify before something if the order (ASC or DESC) of the index is attended.
If not, try using the following command: "dbcc freeproccache" that will
free your procedures cache.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Donald" <Donald.huppe@.bocenor.com> wrote in message
news:0d2301c3721e$c3577560$a101280a@.phx.gbl...
> Hi,
> Since a day, Queries that run on a specific
> table "Transit" are not using the right index.
> I need to add "with (index=X2,readuncommitted)" to the
> query to ensure it uses the right one.
> The first thing i should do is to REINDEX the table.
> BUT, is there something else (less long) i could do
> instead of REINDEX ?
> Thanks,
> Don|||If reindexing works, then most likely running UPDATE STATISTICS will
solve the problem as well.
Gert-Jan
Donald wrote:
> Hi,
> Since a day, Queries that run on a specific
> table "Transit" are not using the right index.
> I need to add "with (index=X2,readuncommitted)" to the
> query to ensure it uses the right one.
> The first thing i should do is to REINDEX the table.
> BUT, is there something else (less long) i could do
> instead of REINDEX ?
> Thanks,
> Don