None 
Author Message
 None

Hi All,

This is my first message ever on news groups :)

I am quite shocked nobody has spotted what I am gonna say, so please do comment if you disagree...
When you join 2 tables, the index of the table on the LEFT of your join is going to be used.

So if one of the 2 tables is massive and has an index which could be used, it MUST be put on the LEFT of the join... You have put it on the right!

Try swaping your join around... I bet at the moment your index on the "stockTick" table is not even used...

Any improvments???

Alix :o)

Quote:
-----Original Message-----

Posted At: 31 December 2003 21:21
Posted To: programming
Conversation: slow query -- help much appreciated
Subject: slow query -- help much appreciated

i am working with the following table

create table stockTick(
    tickID int identity(1,1),
    securityID int,
    timestmp datetime,
    price money,
    volume int
)

this table stores intraday price and volume data for
common stocks.  there are 11 million rows in this table.  

there are two indexes on this table:  

1)  the tickID is a primary key and there is a unique
clustered index on this column.

2)  the second index is a unique nonclustered index on the
securityID and timestmp columns.

i am running a simple select query on this table which is
taking a very long time.  i would appreciate any advice on
if/how i can make this work more quickly.

the query involves a second table of the form:

create table #securityList(
    securityID int,
    startTime datetime,
    endTime datetime
)

the select statement is as follows:

select    l.securityID,
          l.startTime,
          l.endTime,
          t.timestmp,
          t.price,
          t.volume
from      #securityList l join stockTick t on
               l.securityID = t.securityID and
               t.timestmp between l.startTime and l.endTime

this query takes about 5 seconds per row in the
#securityList table when there are 5 rows or less in that
table.  when there are 6 or more rows in that table, this
query takes significantly longer, like a minute per row.

am i doing something wrong here?
what can i do to improve performance here?
should i be making better use of indexes?  should i have
an index just upon the timestmp column?

any help is much appreciated -- thanks in advance!



Sat, 24 Jun 2006 18:03:44 GMT
 None

It all depends on how exactly you are joining the tables. Could you give us
an example?

Along with the join, if you have a WHERE clause, that limits the resultset,
you will most probably see index seeks on both the joined tables, provided
the required indexes are available.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm


Hi All,

This is my first message ever on news groups :)

I am quite shocked nobody has spotted what I am gonna say, so please do
comment if you disagree...
When you join 2 tables, the index of the table on the LEFT of your join is
going to be used.

So if one of the 2 tables is massive and has an index which could be used,
it MUST be put on the LEFT of the join... You have put it on the right!

Try swaping your join around... I bet at the moment your index on the
"stockTick" table is not even used...

Any improvments???

Alix :o)

Quote:
-----Original Message-----

Posted At: 31 December 2003 21:21
Posted To: programming
Conversation: slow query -- help much appreciated
Subject: slow query -- help much appreciated

i am working with the following table

create table stockTick(
    tickID int identity(1,1),
    securityID int,
    timestmp datetime,
    price money,
    volume int
)

this table stores intraday price and volume data for
common stocks.  there are 11 million rows in this table.

there are two indexes on this table:

1)  the tickID is a primary key and there is a unique
clustered index on this column.

2)  the second index is a unique nonclustered index on the
securityID and timestmp columns.

i am running a simple select query on this table which is
taking a very long time.  i would appreciate any advice on
if/how i can make this work more quickly.

the query involves a second table of the form:

create table #securityList(
    securityID int,
    startTime datetime,
    endTime datetime
)

the select statement is as follows:

select    l.securityID,
          l.startTime,
          l.endTime,
          t.timestmp,
          t.price,
          t.volume
from      #securityList l join stockTick t on
               l.securityID = t.securityID and
               t.timestmp between l.startTime and l.endTime

this query takes about 5 seconds per row in the
#securityList table when there are 5 rows or less in that
table.  when there are 6 or more rows in that table, this
query takes significantly longer, like a minute per row.

am i doing something wrong here?
what can i do to improve performance here?
should i be making better use of indexes?  should i have
an index just upon the timestmp column?

any help is much appreciated -- thanks in advance!



Sat, 24 Jun 2006 18:32:54 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. none

2. Finding order where there is none

3. <None>

4. SQL SERVER IN A NONE-NATIVE MODE

5. <None>

6. Can I allow none supported statments?

7. none tech problem

8. Query returning all none nulls

9. <None>

10. Can't get updating subscription to work if sync_type is none

11. How to honor locks but take none?

12. Unusual Errors happening when none did previously


 
Powered by phpBB® Forum Software