?
Solved

SQL Execution Time

Posted on 2013-02-07
4
Medium Priority
?
277 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 2000 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

Technology Partners: 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!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 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