March 27, 2012 at 10:40 am
The problem is it is taking more than 1 hour to execute the query...but the same query takes more than 12 hours on production server..
i have no idea why it takes too much time ....?
Thanks,
Charmer
March 27, 2012 at 10:47 am
There is at least one more table that you did not provide ddl for. PSGADR
This table also appears to be a heap.
_______________________________________________________________
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:47 am
How long does it take to just execute your cte portion?
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
Jared
CE - Microsoft
March 28, 2012 at 2:54 am
SQLKnowItAll (3/27/2012)
How long does it take to just execute your cte portion?
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
It takes 2 and half hours...
Thanks,
Charmer
March 28, 2012 at 3:05 am
Sean Lange (3/27/2012)
There is at least one more table that you did not provide ddl for. PSGADRThis table also appears to be a heap.
Sorry about that Sean..
Here i attached ...
Thanks,
Charmer
March 28, 2012 at 3:19 am
The problem is there is no primary key columns or any unique columns, no index in any of the tables that i deal with...
only very few distinct values in columns...
the table structure is so stupid so that we are not able to get any records so quickly....
NameIDRef column is an unique value column in PSAJCK table and that too I created myself and added it to the table....
I am running out of my ideas...Help me friends..
Thanks,
Charmer
March 28, 2012 at 5:44 am
Table redesign?
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 28, 2012 at 5:51 am
GilaMonster (3/28/2012)
Table redesign?
you want me to redesign the table?
Thanks,
Charmer
March 28, 2012 at 7:14 am
Charmer (3/28/2012)
GilaMonster (3/28/2012)
Table redesign?you want me to redesign the table?
It's a suggestion. Afterall, you said "the table structure is so stupid so that we are not able to get any records so quickly...."
Might be that some redesign will help, or it might not.
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 28, 2012 at 7:24 am
GilaMonster (3/28/2012)
Charmer (3/28/2012)
GilaMonster (3/28/2012)
Table redesign?you want me to redesign the table?
It's a suggestion. Afterall, you said "the table structure is so stupid so that we are not able to get any records so quickly...."
Might be that some redesign will help, or it might not.
But I did what i could .....i have created non clustered index for some columns that i use ....
I have no idea what shall i do to boost the performance...?
I don't have much knowledge about creating well designed table structure....i have posted the DDL's already..so if you don't mind, could you give me some ideas? so that i would try to change the design....
Thanks,
Charmer
March 28, 2012 at 7:48 am
Do you have to interface with a mainframe or some other older system? The reason I ask is all of your names are so short. It is nearly impossible to be able to suggest much in the way of a redesign because all the table and column names are so cryptic.
All of these tables that have no keys are going to perform worse and worse as the amount of data expands. You said something about these tables having millions of records. Without keys you are fighting a losing battle. You could literally have hundreds of rows where every single column is null because every single column allows nulls.
_______________________________________________________________
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 28, 2012 at 7:50 am
For reference here is ddl for the missing table. It would be helpful if in the future you would post this directly in your post instead of attachments (especially Word since a lot of people will not open them).
CREATE TABLE [dbo].[PSGADR](
[GAADR#] [numeric](11, 0) NULL,
[GAGEO#] [numeric](9, 0) NULL,
[GACODE] [nvarchar](1) NULL,
[GASTR#] [nvarchar](8) NULL,
[GAEXTN] [nvarchar](5) NULL,
[GASDIR] [nvarchar](4) NULL,
[GASNAM] [nvarchar](35) NULL,
[GASSUF] [nvarchar](4) NULL,
[GASDR2] [nvarchar](4) NULL,
[GASQLF] [nvarchar](20) NULL,
[GAVENU] [nvarchar](4) NULL,
[GACDIR] [nvarchar](4) NULL,
[GACNAM] [nvarchar](35) NULL,
[GACSUF] [nvarchar](4) NULL,
[GACDR2] [nvarchar](4) NULL,
[GALATD] [numeric](12, 9) NULL,
[GALONG] [numeric](12, 9) NULL,
[GAELEV] [numeric](9, 3) NULL,
[GAAARM] [nvarchar](1) NULL,
[GAACCM] [nvarchar](1) NULL,
[GAACCV] [nvarchar](1) NULL,
[GAAADR] [nvarchar](1) NULL,
[GAJBUS] [nvarchar](1) NULL,
[GAAJCK] [nvarchar](1) NULL,
[GABLDG] [nvarchar](1) NULL,
[GACASE] [nvarchar](1) NULL,
[GACINC] [nvarchar](1) NULL,
[GACLAS] [nvarchar](1) NULL,
[GACNTL] [nvarchar](1) NULL,
[GACVSI] [nvarchar](1) NULL,
[GADTRN] [nvarchar](1) NULL,
[GAE911] [nvarchar](1) NULL,
[GAEHST] [nvarchar](1) NULL,
[GAFINV] [nvarchar](1) NULL,
[GAFORC] [nvarchar](1) NULL,
[GAHOUS] [nvarchar](1) NULL,
[GAINCD] [nvarchar](1) NULL,
[GAJARM] [nvarchar](1) NULL,
[GAJURS] [nvarchar](1) NULL,
[GAMTRN] [nvarchar](1) NULL,
[GANADR] [nvarchar](1) NULL,
[GAOOPA] [nvarchar](1) NULL,
[GAPADR] [nvarchar](1) NULL,
[GAQUIK] [nvarchar](1) NULL,
[GASCHD] [nvarchar](1) NULL,
[GASPVM] [nvarchar](1) NULL,
[GATCKT] [nvarchar](1) NULL,
[GATOWD] [nvarchar](1) NULL,
[GAWRRO] [nvarchar](1) NULL,
[GAFINC] [nvarchar](1) NULL,
[GAFINS] [nvarchar](1) NULL,
[GAFHYD] [nvarchar](1) NULL,
[GAFRNV] [nvarchar](1) NULL,
[GAFSBJ] [nvarchar](1) NULL,
[GAFBLD] [nvarchar](1) NULL,
[GAFCSN] [nvarchar](1) NULL,
[GAFCSF] [nvarchar](1) NULL,
[GAMVSB] [nvarchar](1) NULL,
[GAASBJ] [nvarchar](1) NULL,
[GAEDIT] [nvarchar](1) NULL,
[GAEINC] [nvarchar](1) NULL,
[GAPMTR] [nvarchar](1) NULL,
[GAPTKT] [nvarchar](1) NULL,
[GANBUY] [nvarchar](1) NULL,
[GANEVS] [nvarchar](1) NULL,
[GANINT] [nvarchar](1) NULL,
[GANJRS] [nvarchar](1) NULL,
[GANOFC] [nvarchar](1) NULL,
[GANSRV] [nvarchar](1) NULL,
[GAGJRS] [nvarchar](1) NULL,
[GAGOFC] [nvarchar](1) NULL,
[GAGSBJ] [nvarchar](1) NULL,
[GAACT1] [nvarchar](1) NULL,
[GAACT2] [nvarchar](1) NULL,
[GAACT3] [nvarchar](1) NULL,
[GAACT4] [nvarchar](1) NULL,
[GAACT5] [nvarchar](1) NULL,
[GAACT6] [nvarchar](1) NULL,
[GAACT7] [nvarchar](1) NULL,
[GAACT8] [nvarchar](1) NULL,
[GAACT9] [nvarchar](1) NULL,
[GALCD] [numeric](8, 0) NULL,
[GALCT] [numeric](6, 0) NULL,
[GALCU] [nvarchar](10) NULL,
[Location] [nvarchar](70) NULL,
[MODIFIEDDTTM] [datetime] NULL,
[OldAptNo] [nvarchar](20) NULL
) ON [PRIMARY]
GO
create nonclustered index PSGADR_pnxX1 on PSGADR(gacinc,GAINCD,GAADR#)
create nonclustered index PSGADR_pnxX2 on PSGADR(GAADR#)
_______________________________________________________________
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 28, 2012 at 8:11 am
Sean Lange (3/28/2012)
Do you have to interface with a mainframe or some other older system? The reason I ask is all of your names are so short. It is nearly impossible to be able to suggest much in the way of a redesign because all the table and column names are so cryptic.All of these tables that have no keys are going to perform worse and worse as the amount of data expands. You said something about these tables having millions of records. Without keys you are fighting a losing battle. You could literally have hundreds of rows where every single column is null because every single column allows nulls.
interface with a mainframe? Sean, I don't understand this one....because they gave me the back up of the database and i am working on it...that's all i know...if you brief me, i would ask my manager so that i can give you good answer...
and yes there is no key columns even in a single table that i deal with..but i have to extract the data...i don't know what i am going to do....each query takes more than 3 hours to execute...I'm going crazy......
Thanks,
Charmer
March 28, 2012 at 8:15 am
I modified the query as you friends helped me...the below query works really great when compared to old one....but having loadz of queries that needs to modify with all of your help...
with t as
(
SELECT
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
, J.AJADR#
,M.GAADR
,adr.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
)
--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
, CASE
WHEN AJADR# = 0 then 'No Address, ID = 0'
WHEN AJADR# > 0 and isnull(GAADR, 'nm')='nm' then 'Master Address ID: ' + CAST(AJADR# AS varchar) + ' does not exist'
ELSE location
END AS 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
Thanks,
Charmer
March 28, 2012 at 8:19 am
You may not want to hear this... but I suggest hiring a consultant who can sit down and understand the business requirements and design this properly. We won't be able to do this from the limited amount of information given in the threads. We have tables here with 10's of millions of rows and queries run in seconds joining them. That's because they are designed properly (well... mostly 😉 ). There is only so much that you can do with a query to enhance its performance, after that you have to address the real issue of design.
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply