Solved

SQL Execution Time

Posted on 2013-02-07
4
259 Views
Last Modified: 2013-02-07
I have sql query below. If I remove these fields, the query will execute in about 8 seconds.
,PM.ADDRESS1,PM.ADDRESS2,PM.ADDRESS3,PM.CITY,PM.[STATE],PM.ZIPCODE
However, with these fields it runs for hours. I do not understand this as they are in the same table as PM.VENDORID

-------------------------------------------------------------------
This is the query
-----------------------
SELECT t1.Vendor_ID,t1.Check_Number,t1.Check_Date,Check_Amount,t2.Invoice_Number,t2.Invoice_Amount,t1.VNDCHKNM
,t1.CHEKBKID,PM.VENDNAME,PM.ADDRESS1,PM.ADDRESS2,PM.ADDRESS3,PM.CITY,PM.[STATE],PM.ZIPCODE from (
SELECT t1.VENDORID as Vendor_ID,t1.APFRDCNM as Check_Number,t1.DATE1 as Check_Date,SUM(t1.APFRMAPLYAMT)
AS Check_Amount,t2.VNDCHKNM,t2.CHEKBKID
FROM PM30300 t1
INNER JOIN PM30200 t2 on t1.VCHRNMBR=t2.VCHRNMBR and t1.DOCTYPE=t2.DOCTYPE
WHERE t1.VENDORID LIKE 'R%'
group by t1.APFRDCNM,t1.vendorid,t1.date1,t2.VNDCHKNM, t2.CHEKBKID) t1
INNER JOIN PM00200 pm on T1.Vendor_ID=PM.VENDORID
JOIN (
SELECT VENDORID as Vendor_ID,APFRDCNM as Check_Number,APTODCNM as Invoice_Number,DATE1 as Check_Date,
APFRMAPLYAMT AS Invoice_Amount
FROM PM30300
WHERE VENDORID LIKE 'R%') t2 on t1.Vendor_ID=t2.Vendor_ID and t1.Check_Number=t2.Check_Number

What is causing the complete query to take so long to complete? How can I find out what is causing the delay? We are talking about 250,000 records in the dataset.
0
Comment
Question by:rwheeler23
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38864812
That is odd.

Is table PM00200 a heap, or does it have a clustered index?

How large is that table?  Can you afford to a run sys.dm_db_index_physical_stats with 'DETAILED' on that table?

Just out of curiosity, also please list all the indexes that exist on PM00200.
0
 

Author Comment

by:rwheeler23
ID: 38865743
Here are the indexes. I think the issue has something to do with the date field in the PM30300 table. If I specify a date range, the query runs in less than seconds. Without a date range it runs for hours

 CONSTRAINT [PKPM00200] PRIMARY KEY NONCLUSTERED
(
      [VENDORID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [POWMT]
/****** Object:  Index [AK2PM00200]    Script Date: 02/07/2013 15:49:57 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK2PM00200] ON [dbo].[PM00200]
(
      [VENDNAME] ASC,
      [VENDORID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [POWMT]
/****** Object:  Index [AK3PM00200]    Script Date: 02/07/2013 15:49:57 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK3PM00200] ON [dbo].[PM00200]
(
      [VNDCLSID] ASC,
      [VENDORID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [POWMT]
/****** Object:  Index [AK4PM00200]    Script Date: 02/07/2013 15:49:57 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK4PM00200] ON [dbo].[PM00200]
(
      [USERDEF1] ASC,
      [VENDORID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [POWMT]
/****** Object:  Index [AK5PM00200]    Script Date: 02/07/2013 15:49:57 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK5PM00200] ON [dbo].[PM00200]
(
      [PYMNTPRI] ASC,
      [VENDORID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [POWMT]
/****** Object:  Index [AK6PM00200]    Script Date: 02/07/2013 15:49:57 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK6PM00200] ON [dbo].[PM00200]
(
      [TXRGNNUM] ASC,
      [VENDORID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38865862
Looks like it's a "heap" -- no clustered index at all.

SQL will frequently "get confused" on allocations, etc., with heaps.

You should instead cluster the table on VENDORID, since that's the join you're doing to the table and that column is in virtually every other index already anyway.

That is, drop the PK, and recreate it as CLUSTERED.

Adding a clustered index will force SQL to recreate all the nonclustered indexes.  

You can either do it manually:
    drop all non-clus indexes
    drop the pk constraint
    create the clus index
    recreate all the non-clus indexes

Or just drop the PK constraint and recreate it as clus and SQL will automatically adjust all the non-clus indexes for you.
0
 

Author Closing Comment

by:rwheeler23
ID: 38866083
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mssql SQL query 14 46
How to simplify my SQL statement? 14 50
How to pass a parameter to limit the colums returned in Stored Procedure SQL 2016 2 30
sql Total query 2 18
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now