• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Urgent Help With SQL Query

I have the following query...which needs to  be changed ...First I will post the original query..and then show the two queries that need to be used in place of values on the orginal query to get derived values rather than pulling straight from the table.  THis is a rather slow query so am looking for the fastest way to do this.  Please remember this is SQL 2000.
Thanks.


This is the basic query that will be changed.    The values in the query below for Assigned, Pending, and Available will need to be derived.  I have included two separate queries in the code attachment that show the individual ways at getting at the Assigned and Pending.  

--orginal query....notice that the assigned, pending and then Available values are coming from  a table in this query....I need them to come from the bottom two queries below...I just think there is a better way to join all of this up for speed.  This database is sql server 2000.  

SELECT A.ACN, 
         A.AUTHORIZED, 
         AA.ASSIGNED, 
         AA.PENDING, 
         (A.AUTHORIZED - (AA.ASSIGNED + AA.PENDING)) As Available, 
         AR.FILE_NAME 
      FROM ACN A       
            INNER JOIN ACN_ACCOUNT AA 
                  ON A.ACN = AA.ACN 
            INNER JOIN ACN_REQUEST AR 
                  ON A.ACN = AR.ACN 
				  
				  
				  
				  
				  
---assigned

SELECT     COUNT(SERIAL_NUMBER) AS ASSIGNED, ACN, ACCOUNT_ID
FROM         dbo.ACN_SAASM
GROUP BY ACN, ACCOUNT_ID

--pending

SELECT     TOP 100 PERCENT s.ACN, SUBSTRING(sd.SHIPMENT_ID, 1, 7) AS ACCOUNT_ID, COUNT(sd.SERIAL_NUMBER) AS PENDING
FROM         dbo.SHIPMENT_DETAIL AS sd INNER JOIN
                      dbo.SHIPMENT AS s ON sd.SHIPMENT_ID = s.SHIPMENT_ID LEFT OUTER JOIN
                      dbo.ACN_SAASM AS A ON SUBSTRING(sd.SERIAL_NUMBER, 4, 11) = A.SERIAL_NUMBER AND SUBSTRING(sd.SHIPMENT_ID, 1, 7) 
                      = A.ACCOUNT_ID
WHERE     (A.SERIAL_NUMBER IS NULL) AND (sd.SHIP_STATUS_CODE <> 1) AND (LEN(s.ACN) = 11)
GROUP BY SUBSTRING(sd.SHIPMENT_ID, 1, 7), s.ACN
ORDER BY s.ACN, SUBSTRING(sd.SHIPMENT_ID, 1, 7)

Open in new window

0
Robb Hill
Asked:
Robb Hill
  • 4
  • 4
1 Solution
 
ZberteocCommented:
Try below.
SELECT 
	A.ACN, 
	A.AUTHORIZED, 
	AA.ASSIGNED, 
	AA.PENDING, 
	(A.AUTHORIZED - (AA.ASSIGNED + AA.PENDING)) As Available, 
	AR.FILE_NAME 
FROM 
	ACN A       
    INNER JOIN ACN_ACCOUNT AA 
          ON A.ACN = AA.ACN 
    INNER JOIN ACN_REQUEST AR 
          ON A.ACN = AR.ACN 
	
	-- get the assigned here                  
    inner join 
    (
		SELECT     
			COUNT(SERIAL_NUMBER) AS ASSIGNED, ACN, ACCOUNT_ID
		FROM         
			dbo.ACN_SAASM
		GROUP BY 
			ACN, 
			ACCOUNT_ID
    ) assigned
		on	assigned.ACCOUNT_ID=AA.ACCOUNT_ID
		and assigned.ACN=AA.ACN
		
	-- get the pending here                  
	inner join 
	(
		SELECT     
			s.ACN, 
			SUBSTRING(sd.SHIPMENT_ID, 1, 7) AS ACCOUNT_ID, 
			COUNT(sd.SERIAL_NUMBER) AS PENDING
		FROM         
			dbo.SHIPMENT_DETAIL AS sd 
			INNER JOIN dbo.SHIPMENT AS s 
				ON sd.SHIPMENT_ID = s.SHIPMENT_ID 
			LEFT OUTER JOIN dbo.ACN_SAASM AS A 
				ON SUBSTRING(sd.SERIAL_NUMBER, 4, 11) = A.SERIAL_NUMBER 
				AND SUBSTRING(sd.SHIPMENT_ID, 1, 7) = A.ACCOUNT_ID
		WHERE     
			(A.SERIAL_NUMBER IS NULL) 
			AND (sd.SHIP_STATUS_CODE <> 1) 
			AND (LEN(s.ACN) = 11)
		GROUP BY 
			SUBSTRING(sd.SHIPMENT_ID, 1, 7), 
			s.ACN
	) pending
		on	pending.ACCOUNT_ID=AA.ACCOUNT_ID
		and	pending.ACN=AA.ACN

Open in new window

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
I am assuming where you have aa.assigned this would  be assigned.assigned...right?
0
 
ZberteocCommented:
I assumed that he ACN_ACCOUNT table has an ACCOUNT_ID column, if not we can fix it.

One reason the query is slow is because uses SUBSTRING function to JOIN and to GROUP BY, which will not make use of the indexes on the columns from substring.

Make sure you have the following indexes on your tables:


ACN_ACCOUNT.ACT
ACN_ACCOUNT.ACCOUNT_ID                          
                         
ACN_REQUEST.ACN
                         
ACN_SAASM.(ACN+ACCOUNT_ID)                          
ACN_SAASM.ACCOUNT_ID                   
ACN_SAASM.SERIAL_NUMBER                          

SHIPMENT_DETAIL.SHIPMENT_ID
SHIPMENT_DETAIL.SHIP_STATUS_CODE
SHIPMENT_DETAIL.SERIAL_NUMBER

SHIPMENT.SHIPMENT_ID

where ACN_SAASM.(ACN+ACCOUNT_ID) is a composite index
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
ZberteocCommented:
robbhill:
"I am assuming where you have aa.assigned this would  be assigned.assigned...right?"

Correct, and also AA.PENDING should be pending.PENDING.

Here is the query again:


SELECT 
	A.ACN, 
	A.AUTHORIZED, 
	assigned.ASSIGNED, 
	pending.PENDING, 
	(A.AUTHORIZED - (assigned.ASSIGNED + pending.PENDING)) As Available, 
	AR.FILE_NAME 
FROM 
	ACN A       
    INNER JOIN ACN_ACCOUNT AA 
          ON A.ACN = AA.ACN 
    INNER JOIN ACN_REQUEST AR 
          ON A.ACN = AR.ACN 
	
	-- get the assigned here                  
    inner join 
    (
		SELECT     
			ACN, 
			ACCOUNT_ID,
			COUNT(SERIAL_NUMBER) AS ASSIGNED 
		FROM         
			dbo.ACN_SAASM
		GROUP BY 
			ACN, 
			ACCOUNT_ID
    ) assigned
		on	assigned.ACCOUNT_ID=AA.ACCOUNT_ID
		and assigned.ACN=AA.ACN
		
	-- get the pending here                  
	inner join 
	(
		SELECT     
			s.ACN, 
			SUBSTRING(sd.SHIPMENT_ID, 1, 7) AS ACCOUNT_ID, 
			COUNT(sd.SERIAL_NUMBER) AS PENDING
		FROM         
			dbo.SHIPMENT_DETAIL AS sd 
			INNER JOIN dbo.SHIPMENT AS s 
				ON sd.SHIPMENT_ID = s.SHIPMENT_ID 
			LEFT OUTER JOIN dbo.ACN_SAASM AS A 
				ON SUBSTRING(sd.SERIAL_NUMBER, 4, 11) = A.SERIAL_NUMBER 
				AND SUBSTRING(sd.SHIPMENT_ID, 1, 7) = A.ACCOUNT_ID
		WHERE     
			(A.SERIAL_NUMBER IS NULL) 
			AND (sd.SHIP_STATUS_CODE <> 1) 
			AND (LEN(s.ACN) = 11)
		GROUP BY 
			SUBSTRING(sd.SHIPMENT_ID, 1, 7), 
			s.ACN
	) pending
		on	pending.ACCOUNT_ID=AA.ACCOUNT_ID
		and	pending.ACN=AA.ACN

Open in new window

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
It does have an account_ID column....but if you are referencing the assigned value from that table and not the alias table...one is derived and the other is hardcoded...trying to only use the derived....so should we change that....mine looks like the following...is this overkill?
SELECT     A.ACN, A.AUTHORIZED, assigned.ASSIGNED, pending.PENDING, A.AUTHORIZED - (assigned.ASSIGNED + pending.PENDING) AS Available, 
                      AR.FILE_NAME
FROM         dbo.ACN AS A INNER JOIN
                      dbo.ACN_ACCOUNT AS AA ON A.ACN = AA.ACN INNER JOIN
                      dbo.ACN_REQUEST AS AR ON A.ACN = AR.ACN INNER JOIN
                          (SELECT     COUNT(SERIAL_NUMBER) AS ASSIGNED, ACN, ACCOUNT_ID
                            FROM          dbo.ACN_SAASM
                            GROUP BY ACN, ACCOUNT_ID) AS assigned ON assigned.ACCOUNT_ID = AA.ACCOUNT_ID AND assigned.ACN = AA.ACN INNER JOIN
                          (SELECT     s.ACN, SUBSTRING(sd.SHIPMENT_ID, 1, 7) AS ACCOUNT_ID, COUNT(sd.SERIAL_NUMBER) AS PENDING
                            FROM          dbo.SHIPMENT_DETAIL AS sd INNER JOIN
                                                   dbo.SHIPMENT AS s ON sd.SHIPMENT_ID = s.SHIPMENT_ID LEFT OUTER JOIN
                                                   dbo.ACN_SAASM AS A ON SUBSTRING(sd.SERIAL_NUMBER, 4, 11) = A.SERIAL_NUMBER AND SUBSTRING(sd.SHIPMENT_ID, 1, 7) 
                                                   = A.ACCOUNT_ID
                            WHERE      (A.SERIAL_NUMBER IS NULL) AND (sd.SHIP_STATUS_CODE <> 1) AND (LEN(s.ACN) = 11)
                            GROUP BY SUBSTRING(sd.SHIPMENT_ID, 1, 7), s.ACN) AS pending ON pending.ACCOUNT_ID = AA.ACCOUNT_ID AND pending.ACN = AA.ACN

Open in new window

0
 
ZberteocCommented:
I think you missed my previous comment.

Please don't change the the query formatting. I worked enough on it.

Thank you.
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
oh I didnt mean to paste it that way...I have the formatting that way...just made it to a view and the formatting got all screwy on my paste.
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
Thank you very much!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now