Solved

SQL Execution Time

Posted on 2013-02-07
4
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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