March 27, 2012 at 9:59 am
Hi Friends..
I am having kinda same problem...
I have more than 7 million records in a table.. i was wondering to see how much time to execute and [Ermm] it takes more than 15 minutes and it was keep on going...
and the more saddest part is i have to join with another table for three times which has 4.5 million records.....
I have no idea how much time it is going to take for execution....
I hope i can't do much with this...but give me some ideas friends...i tried the above said script on production server and it took more than 12 hours and did not completed the execution...
Friends, Give me your idea to increase the performance of the query? it frustrates me...
let me know if anything needed from me...
Thanks,
Charmer
March 27, 2012 at 10:04 am
What is your query? Please post DDL and DML.
Jared
CE - Microsoft
March 27, 2012 at 10:08 am
SQLKnowItAll (3/27/2012)
What is your query? Please post DDL and DML.
This is my Query
with t as
(
SELECT --COUNT(*)
j.NameIDRef
--, 0 AS SEQNUM
, ROW_NUMBER() over (partition by Juris.PnxCodeValue, j.NameIDRef order by galcd+galct desc) -1 seqnum
, ADR.GASTR# AS PREMISE
, A.PnxCodeValue AS PREFIX
, ADR.GASNAM AS STREETNAME
, B.PnxCodeValue as STREETTYPE
, ADR.GASQLF AS APTNO
, CASE
WHEN AJADR# = 0 then 'No Address, ID = 0'
WHEN AJADR# > 0 and isnull(M.GAADR, 'nm')='nm' then 'Master Address ID: ' + CAST(AJADR# AS varchar) + ' does not exist'
ELSE adr.location
END AS LOCATION
, J.AJCITY AS CITY
, C.PnxCodeValue AS STATE
, J.AJZIP AS ZIP
, ADR.GALCU AS MODIFIEDPFIDREF
, adr.modifieddttm AS MODIFIEDDTTM
, Juris.PnxCodeValue as JurisID
From
MidPolice.dbo.SrcNameDtl ND
join SrcCodeMap Juris
on nd.JurisID = Juris.PnxCodeValue
and Juris.CodeID = 100
JOIN PSAJCK AS J
on j.NameIDRef = nd.NameIDRef
and AJORI# = Juris.SrcCodeValue
left outer join PSGADR AS ADR
ON J.AJADR# = ADR.GAADR#
Left outer Join SrcCodeMap A
on A.SrcCodeValueRef = adr.GASDIR
and A.CodeID = 2071 -- Prefix
Left outer Join SrcCodeMap B
on B.SrcCodeValueRef = adr.GASSUF
and B.CodeID = 6001 -- StreetType
Left outer Join SrcCodeMap C
on C.SrcCodeValueRef = j.AJSTAT
and C.CodeID = 2009 -- state
cross apply (
select distinct
cast(GAADR# as varchar) GAADR
from
PSGADR
where
GAADR# = j.AJADR#) M
)
SELECT
t.nameidref
, seqnum
, ltrim(rtrim(PREMISE))
, ltrim(rtrim(PREFIX))
, STREETNAME
, ltrim(rtrim(stREETTYPE))
, APTNO
, t.LOCATION
, CITY
, left(ltrim(rtrim(STATE)),2)
, ltrim(rtrim(ZIP))
, t.MODIFIEDPFIDREF
, t.MODIFIEDDTTM
, t.JurisID
FROM
t
JOIN MidPolice.dbo.SrcNameDtl snd
on t.nameidref = snd.NameIDRef
AND t.JurisID = snd.JurisID
where
t.seqnum = 0
Thanks,
Charmer
March 27, 2012 at 10:12 am
1. Why are you using that case statement in your CTE? Seems useless at that point.
2. Why are you using the row_number in your CTE? It doesn't look to be used anywhere else in the DML.
Jared
CE - Microsoft
March 27, 2012 at 10:13 am
Here is the ddl...
GO
/****** Object: Table [dbo].[PSAJCK] Script Date: 03/27/2012 21:32:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PSAJCK](
[AJORI#] [nvarchar](9) NULL,
[AJPID#] [numeric](9, 0) NULL,
[AJJTYP] [nvarchar](1) NULL,
[AJLNAM] [nvarchar](50) NULL,
[AJFNAM] [nvarchar](25) NULL,
[AJMNAM] [nvarchar](15) NULL,
[AJNSUF] [nvarchar](4) NULL,
[AJDOB] [numeric](8, 0) NULL,
[AJAGE] [numeric](3, 0) NULL,
[AJAGEU] [numeric](3, 0) NULL,
[AJAGED] [nvarchar](1) NULL,
[AJRACE] [nvarchar](4) NULL,
[AJSEX] [nvarchar](4) NULL,
[AJHAIR] [nvarchar](4) NULL,
[AJEYES] [nvarchar](4) NULL,
[AJHGT] [numeric](3, 2) NULL,
[AJHGTU] [numeric](3, 2) NULL,
[AJWGT] [numeric](3, 0) NULL,
[AJWGTU] [numeric](3, 0) NULL,
[AJSS#] [numeric](9, 0) NULL,
[AJPHOT] [nvarchar](1) NULL,
[AJDVL#] [nvarchar](20) NULL,
[AJDVLS] [nvarchar](4) NULL,
[AJDVLT] [nvarchar](4) NULL,
[AJDVLY] [numeric](4, 0) NULL,
[AJADR#] [numeric](11, 0) NULL,
[AJCITY] [nvarchar](25) NULL,
[AJSTAT] [nvarchar](4) NULL,
[AJZIP] [nvarchar](10) NULL,
[AJLCOD] [nvarchar](10) NULL,
[AJFCOD] [nvarchar](10) NULL,
[AJALRM] [nvarchar](1) NULL,
[AJCORT] [nvarchar](1) NULL,
[AJPLAN] [nvarchar](1) NULL,
[AJFR04] [nvarchar](1) NULL,
[AJFR05] [nvarchar](1) NULL,
[AJFR06] [nvarchar](1) NULL,
[AJFR07] [nvarchar](1) NULL,
[AJFR08] [nvarchar](1) NULL,
[AJFR09] [nvarchar](1) NULL,
[AJFR10] [nvarchar](1) NULL,
[AJFR11] [nvarchar](1) NULL,
[AJFR12] [nvarchar](1) NULL,
[AJFR13] [nvarchar](1) NULL,
[AJFR14] [nvarchar](1) NULL,
[AJFR15] [nvarchar](1) NULL,
[AJFR16] [nvarchar](1) NULL,
[AJFR17] [nvarchar](1) NULL,
[AJFR18] [nvarchar](1) NULL,
[AJFR19] [nvarchar](1) NULL,
[AJFR20] [nvarchar](1) NULL,
[AJFR21] [nvarchar](1) NULL,
[AJFR22] [nvarchar](1) NULL,
[AJFR23] [nvarchar](1) NULL,
[AJFR24] [nvarchar](1) NULL,
[AJFR25] [nvarchar](1) NULL,
[AJFR26] [nvarchar](1) NULL,
[AJFR27] [nvarchar](1) NULL,
[AJFR28] [nvarchar](1) NULL,
[AJFR29] [nvarchar](1) NULL,
[AJFR30] [nvarchar](1) NULL,
[AJFR31] [nvarchar](1) NULL,
[AJFR32] [nvarchar](1) NULL,
[AJFR33] [nvarchar](1) NULL,
[AJFR34] [nvarchar](1) NULL,
[AJFR35] [nvarchar](1) NULL,
[AJFR36] [nvarchar](1) NULL,
[AJFR37] [nvarchar](1) NULL,
[AJFR38] [nvarchar](1) NULL,
[AJFR39] [nvarchar](1) NULL,
[AJFR40] [nvarchar](1) NULL,
[AJLCD] [numeric](8, 0) NULL,
[AJLCT] [numeric](6, 0) NULL,
[AJLCU] [nvarchar](10) NULL,
[NameIDRef] [varchar](15) NULL,
[JurisID] [varchar](4) NULL,
[MODIFIEDDTTM] [datetime] NULL,
[DOBDTTM] [datetime] NULL,
[FULLNAME] [nvarchar](70) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/*
* TABLE: SrcCodeMap
*/
CREATE TABLE SrcCodeMap(
JurisID char(4) NOT NULL,
CodeID int NOT NULL,
SrcCodeValueRef varchar(15) NOT NULL,
SrcCodeValue varchar(40) NOT NULL,
SrcCodeDescr varchar(150) NULL,
PnxCodeValue char(10) NULL,
SysCodeValue char(10) NULL,
NewCodeValue char(10) NULL,
VendorCode char(2) NULL,
Misc1 varchar(255) NULL,
Misc2 varchar(255) NULL
)
go
IF OBJECT_ID('SrcCodeMap') IS NOT NULL
PRINT '<<< CREATED TABLE SrcCodeMap >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE SrcCodeMap >>>'
go
/*
* INDEX: SrcCodeMap_x1
*/
CREATE UNIQUE INDEX SrcCodeMap_x1 ON SrcCodeMap(CodeID, SrcCodeValueRef, SrcCodeDescr, PnxCodeValue)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('SrcCodeMap') AND name='SrcCodeMap_x1')
PRINT '<<< CREATED INDEX SrcCodeMap.SrcCodeMap_x1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX SrcCodeMap.SrcCodeMap_x1 >>>'
go
/*
* INDEX: SrcCodeMap_x2
*/
CREATE INDEX SrcCodeMap_x2 ON SrcCodeMap(SrcCodeValueRef, CodeID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('SrcCodeMap') AND name='SrcCodeMap_x2')
PRINT '<<< CREATED INDEX SrcCodeMap.SrcCodeMap_x2 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX SrcCodeMap.SrcCodeMap_x2 >>>'
go
/*
* INDEX: SrcCodeMap_x3
*/
CREATE INDEX SrcCodeMap_x3 ON SrcCodeMap(SrcCodeValue, CodeID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('SrcCodeMap') AND name='SrcCodeMap_x3')
PRINT '<<< CREATED INDEX SrcCodeMap.SrcCodeMap_x3 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX SrcCodeMap.SrcCodeMap_x3 >>>'
go
/****** Object: Table [dbo].[SrcNameDtl] Script Date: 03/27/2012 21:34:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SrcNameDtl](
[JurisID] [char](4) NOT NULL,
[NameIDRef] [char](15) NOT NULL,
[NameType] [char](1) NOT NULL,
[NameTypeID] [bigint] NULL,
[Sex] [char](1) NULL,
[Race] [char](1) NULL,
[Ethnic] [char](1) NULL,
[Height] [int] NULL,
[HeightDttm] [datetime] NULL,
[Weight] [int] NULL,
[WeightDttm] [datetime] NULL,
[DOBDttm] [datetime] NULL,
[JacketNo] [varchar](14) NULL,
[FBINo] [varchar](20) NULL,
[SBINo] [varchar](20) NULL,
[FingerPrint] [varchar](20) NULL,
[DNAType] [varchar](20) NULL,
[BirthCity] [varchar](25) NULL,
[BirthCounty] [varchar](30) NULL,
[BirthState] [char](2) NULL,
[BirthCountry] [char](2) NULL,
[Resident] [char](1) NULL,
[Citizenship1] [char](2) NULL,
[Citizenship2] [char](2) NULL,
[PassportNo1] [varchar](20) NULL,
[PassportNo2] [varchar](20) NULL,
[ImmigrationNo] [varchar](20) NULL,
[EyeColor] [char](3) NULL,
[EyeColorDttm] [datetime] NULL,
[HairColor] [char](3) NULL,
[HairColorDttm] [datetime] NULL,
[Skin] [char](3) NULL,
[Build] [char](3) NULL,
[BuildDttm] [datetime] NULL,
[Condition] [char](1) NULL,
[ConditionDttm] [datetime] NULL,
[Handy] [char](1) NULL,
[Jail] [char](1) NULL,
[MaritalStatus] [char](1) NULL,
[IsPhoto] [smallint] NULL,
[PhotoDttm] [datetime] NULL,
[DeceasedDttm] [datetime] NULL,
[Reason] [char](1) NULL,
[Civil] [char](2) NULL,
[BusinessType] [char](4) NULL,
[BusinessID] [varchar](20) NULL,
[SSNo] [varchar](15) NULL,
[Email] [varchar](100) NULL,
[PhoneType1] [char](15) NULL,
[PhoneNbr1] [varchar](20) NULL,
[PhoneType2] [char](1) NULL,
[PhoneNbr2] [varchar](20) NULL,
[Teeth] [char](3) NULL,
[TeethDttm] [datetime] NULL,
[Hand] [char](3) NULL,
[CreatedPFIDRef] [char](10) NULL,
[CreatedDttm] [datetime] NULL,
[ModifiedPFIDRef] [char](10) NULL,
[ModifiedDttm] [datetime] NULL,
[Custom1] [varchar](255) NULL,
[Custom2] [varchar](255) NULL,
[XRef1] [varchar](255) NULL,
CONSTRAINT [SrcNameDtl_PK] PRIMARY KEY CLUSTERED
(
[JurisID] ASC,
[NameIDRef] 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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2012 at 10:18 am
You use ROW_NUMBER(...) AS seqnum. Then you join and filter the CTE using WHERE seqnumber = 0. What a waste! Just find the 1 row you want from the cte instead of all the rows. Then use that information get your results.
Jared
CE - Microsoft
March 27, 2012 at 10:23 am
nvarchar(1)... not actually a performance issue but really???
All columns nullable...
No primary keys...
No indexes at all on at least 1 of the tables...
You pick the performance problems at the ddl level. Add to that all the dml performance issues pointed out...
Yeah this is going to be super painfully slow.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2012 at 10:23 am
SQLKnowItAll (3/27/2012)
You use ROW_NUMBER(...) AS seqnum. Then you join and filter the CTE using WHERE seqnumber = 0. What a waste! Just find the 1 row you want from the cte instead of all the rows. Then use that information get your results.
OOps, sorry i missed some statements....here is my full query....
with t as
(
SELECT --COUNT(*)
j.NameIDRef
, 0 AS SEQNUM
, ADR.GASTR# AS PREMISE
, A.PnxCodeValue AS PREFIX
, ADR.GASNAM AS STREETNAME
, B.PnxCodeValue as STREETTYPE
, ADR.GASQLF AS APTNO
, CASE
WHEN AJADR# = 0 then 'No Address, ID = 0'
WHEN AJADR# > 0 and isnull(M.GAADR, 'nm')='nm' then 'Master Address ID: ' + CAST(AJADR# AS varchar) + ' does not exist'
ELSE adr.location
END AS LOCATION
, J.AJCITY AS CITY
, C.PnxCodeValue AS STATE
, J.AJZIP AS ZIP
, ADR.GALCU AS MODIFIEDPFIDREF
, adr.modifieddttm AS MODIFIEDDTTM
, Juris.PnxCodeValue as JurisID
From
GloucMidPolice.dbo.SrcNameDtl ND
join SrcCodeMap Juris
on nd.JurisID = Juris.PnxCodeValue
and Juris.CodeID = 100
JOIN PSAJCK AS J
on j.NameIDRef = nd.NameIDRef
and AJORI# = Juris.SrcCodeValue
left outer join PSGADR AS ADR
ON J.AJADR# = ADR.GAADR#
Left outer Join SrcCodeMap A
on A.SrcCodeValueRef = adr.GASDIR
and A.CodeID = 2071 -- Prefix
Left outer Join SrcCodeMap B
on B.SrcCodeValueRef = adr.GASSUF
and B.CodeID = 6001 -- StreetType
Left outer Join SrcCodeMap C
on C.SrcCodeValueRef = j.AJSTAT
and C.CodeID = 2009 -- state
cross apply (
select distinct
cast(GAADR# as varchar) GAADR
from
PSGADR
where
GAADR# = j.AJADR#) M
)
--insert into MidPolice.dbo.srcnameaddr
--(
--nameidref
--, seqno
--, Premise
--, PREFIX
--, streetname
--, streettype
--, aptno
--, location
--, city
--, state
--, zip
--, modifiedpfidref
--, modifieddttm
--, JurisID
--)
SELECT
t.nameidref
, seqnum
, ltrim(rtrim(PREMISE))
, ltrim(rtrim(PREFIX))
, STREETNAME
, ltrim(rtrim(stREETTYPE))
, APTNO
, t.LOCATION
, CITY
, left(ltrim(rtrim(STATE)),2)
, ltrim(rtrim(ZIP))
, t.MODIFIEDPFIDREF
, t.MODIFIEDDTTM
, t.JurisID
FROM
t
JOIN MidPolice.dbo.SrcNameDtl snd
on t.nameidref = snd.NameIDRef
AND t.JurisID = snd.JurisID
where
t.seqnum = 0
Thanks,
Charmer
March 27, 2012 at 10:27 am
Ok, now you just set all rows = 0 for seqnumber, and then you filter on that. Why? Take out that column in the cte and take out the where seqnum = 0 in your final result set. That's just a start.
I would also take out all of your case statements in your CTE that do things for reporting. Do your case statement in the final query, if you really must.
Jared
CE - Microsoft
March 27, 2012 at 10:27 am
Sean Lange (3/27/2012)
nvarchar(1)... not actually a performance issue but really???All columns nullable...
No primary keys...
No indexes at all on at least 1 of the tables...
You pick the performance problems at the ddl level. Add to that all the dml performance issues pointed out...
Yeah this is going to be super painfully slow.
Yes...but i have created indexes for 8 columns in PSAJCK, and 1 in SrcNameDtl...even though the query is executing very slow...PSAJCK table has 7 million records...
Thanks,
Charmer
March 27, 2012 at 10:29 am
Charmer (3/27/2012)
Sean Lange (3/27/2012)
nvarchar(1)... not actually a performance issue but really???All columns nullable...
No primary keys...
No indexes at all on at least 1 of the tables...
You pick the performance problems at the ddl level. Add to that all the dml performance issues pointed out...
Yeah this is going to be super painfully slow.
Yes...but i have created indexes for 8 columns in PSAJCK, and 1 in SrcNameDtl...even though the query is executing very slow...PSAJCK table has 7 million records...
What are the indexes on PSAJCK? You didn't post any of them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2012 at 10:29 am
Execution plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 10:31 am
Please attach your execution plan
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 27, 2012 at 10:33 am
Sean Lange (3/27/2012)
Charmer (3/27/2012)
Sean Lange (3/27/2012)
nvarchar(1)... not actually a performance issue but really???All columns nullable...
No primary keys...
No indexes at all on at least 1 of the tables...
You pick the performance problems at the ddl level. Add to that all the dml performance issues pointed out...
Yeah this is going to be super painfully slow.
Yes...but i have created indexes for 8 columns in PSAJCK, and 1 in SrcNameDtl...even though the query is executing very slow...PSAJCK table has 7 million records...
What are the indexes on PSAJCK? You didn't post any of them.
Create index psajck_pnxX1 on psajck ( AJORI#)
Create index psajck_pnxX2 on psajck ( AJSEX)
Create index psajck_pnxX3 on psajck ( AJRACE)
Create index psajck_pnxX4 on psajck ( AJEYES)
Create index psajck_pnxX5 on psajck ( AJHAIR)
Create index psajck_pnxX6 on psajck ( AJNSUF)
Create index psajck_pnxX7 on psajck ( AJDVLS)
Create index psajck_pnxX8 on psajck ( AJJTYP, AJPID#)
Create index psajck_pnxX9 on psajck ( AJJTYP,AJPID#,AJORI#)
Create index psajck_pnxX10 on psajck ( nameidref)
Thanks,
Charmer
March 27, 2012 at 10:34 am
GilaMonster (3/27/2012)
Execution plan please?
I am not able to get the execution plan with data since it is taking too much time...
Thanks,
Charmer
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply