Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| Search | Today's Posts | Mark Forums Read
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
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 4th, 2008, 11:17 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old January 8th, 2008, 07:55 PM
Authorized User
 
Join Date: Sep 2007
Location: , , .
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
Reply With Quote
  #3 (permalink)  
Old January 8th, 2008, 08:18 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
  #4 (permalink)  
Old January 9th, 2008, 03:28 PM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
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

Reply With Quote
  #5 (permalink)  
Old January 9th, 2008, 08:04 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
  #6 (permalink)  
Old January 10th, 2008, 11:03 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
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

Reply With Quote
  #7 (permalink)  
Old January 10th, 2008, 12:36 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
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.

Reply With Quote
  #8 (permalink)  
Old January 10th, 2008, 12:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
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
Reply With Quote
  #9 (permalink)  
Old January 10th, 2008, 12:58 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
  #10 (permalink)  
Old January 10th, 2008, 01:06 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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.