Solved

SQL Execution Time

Posted on 2013-02-07
4
267 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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