Hi to all...
I need some help with the following select statement...
I what to see if the Qty are equal in two different tables...
It seems to be working just that its taking a long time to finish...

SELECT Sum(G.Qty)-sum(abs(T.Qty)*T.PosNeg)
FROM Table1 G
JOIN Table2 T ON T.Date = G.Date
WHERE T.Date Between '11/08/07' and '11/08/07'

Not sure if its not correct or I should put it in a store procedure..

Thanking you in advance...

Rino

How many records are in the tables?
How many records are produced on your join?

Also you are using the between function (will it accept a range of dates...your example does not)...if it won't be a range don't use between.

Also have a look at the stored procedures sp_who2 and sp_lock to get performance information when your queries are running

http://mynameissteve.com

The use of strings as dates forces implicit conversions which makes it impossible to use indexes correctly...




--Jeff Moden

Thank you for getting back to me...

I created table1 which is grouped data from table2 which I use it in my intranet so that it doesn't have must to search for...
That why sometimes I check if they balance...
In table1 i have 39,841,843 records
and table2 i have 88,349,767 records...

I have to take a look at those two store procedure because don't understand them that much...

What should I used to benefit from the index keys...

Thank you
Rino

Convert the explicit date strings in your WHERE clause to things like CAST('11/08/07' AS DATETIME) or, better yet, create some date variables and use those instead. Also, you might want to get out of the habit of using 2 digit years.

--Jeff Moden

Thank you...
I'll take a look and try it...

One other question...
Would you know if there's already a store procedure that I can run that tells me all the tables/size that I have in my database...

Thanking you in advance

Rino

To answer your question about what benefit will indexes give. If your database is indexed correctly your queries will run incredibly faster.

...and if your queries are correctly constructed to utilize those indexes ... :)

Jeff Mason
[email protected]

... but only if your queries are capable of actually using the indexes. Just throwing indexes on a table does not guarantee that they will be used correctly.

--Jeff Moden

So, use your correctly constructed queries to build your indexes.