Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 4th, 2008, 11:17 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default select statement help...

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

 
Old January 8th, 2008, 07:55 PM
Authorized User
 
Join Date: Sep 2007
Posts: 56
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old January 8th, 2008, 08:18 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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




--Jeff Moden
 
Old January 9th, 2008, 03:28 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

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

 
Old January 9th, 2008, 08:04 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old January 10th, 2008, 11:03 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

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

 
Old January 10th, 2008, 12:36 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

 
Old January 10th, 2008, 12:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by rstelma
 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
je.mason@comcast.net
 
Old January 10th, 2008, 12:58 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

... 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
 
Old January 10th, 2008, 01:06 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
select statement ??? RinoDM SQL Server 2000 7 June 19th, 2008 08:40 AM
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
select statement help RinoDM SQL Server 2000 2 January 16th, 2007 11:35 AM
Select Statement jmss66 Oracle 1 May 27th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.