SQL Execution Time

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.
rwheeler23Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
rwheeler23Author Commented:
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
 
rwheeler23Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.