March 22, 2012 at 12:11 pm
Lynn Pettis (3/22/2012) In fact, in production code I even use table aliases in single table queries. You never know when you may have to add a second table to the query to meet a change in requirements.
Brilliant! Always good to think ahead!
Jared
CE - Microsoft
March 22, 2012 at 12:38 pm
I made a minor change to the code, not sure how it will affect performance as I have nothing to test it against.
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
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 GloucMidPolice.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 GloucMidPolice.dbo.SrcNameDtl snd
on t.nameidref = snd.NameIDRef
AND t.JurisID = snd.JurisID
where
t.seqnum = 0
March 22, 2012 at 12:47 pm
Lynn Pettis (3/22/2012)
I made a minor change to the code, not sure how it will affect performance as I have nothing to test it against.
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
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 GloucMidPolice.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 GloucMidPolice.dbo.SrcNameDtl snd
on t.nameidref = snd.NameIDRef
AND t.JurisID = snd.JurisID
where
t.seqnum = 0
Thanks Lynn...I will work on this and il let you know about the speed...i'll even try to get sample DDL's but it is on production server and they don't allow us to get DDLs but il try my best by saying this performance issue...
Thanks,
Charmer
March 22, 2012 at 12:49 pm
What do you test your code on during development? You don't have a development or test database to work with?
March 22, 2012 at 1:51 pm
Lynn Pettis (3/22/2012)
What do you test your code on during development? You don't have a development or test database to work with?
Even taking the DDL from the dev/test server would be helpful.
With significantly different performance between the two, I would still want the production version of the DDL. Something tells me one is not entirely like the other.
If not DDL, then execution plans at the very least for both the faster and slower environments.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2012 at 2:18 pm
Even if SSIS it is not faster the way that I use it is more (a lot mor :-D) to make all kind of sql tastks with it.
March 23, 2012 at 3:16 am
Lynn Pettis (3/22/2012)
What do you test your code on during development? You don't have a development or test database to work with?
they make a copy of their Live database every day on their server...and they asking us to work on their server..and more over the size of the database is too big..it is more than 100 Gb...so getting the database from their server is much difficult for me...it may take two days i am sure...
Thanks,
Charmer
March 23, 2012 at 3:25 am
SQLRNNR (3/22/2012)
Lynn Pettis (3/22/2012)
What do you test your code on during development? You don't have a development or test database to work with?Even taking the DDL from the dev/test server would be helpful.
With significantly different performance between the two, I would still want the production version of the DDL. Something tells me one is not entirely like the other.
If not DDL, then execution plans at the very least for both the faster and slower environments.
the problem is the tables i deal with are more than 7 million records...so i am not able to get the DDL's so quickly...
is it possible to get the DDL's based on our own counts needed?
i don't find any filter condition to specify about how many records of DDL's that i actually need...
Thanks,
Charmer
March 23, 2012 at 3:34 am
Charmer (3/23/2012)
SQLRNNR (3/22/2012)
Lynn Pettis (3/22/2012)
What do you test your code on during development? You don't have a development or test database to work with?Even taking the DDL from the dev/test server would be helpful.
With significantly different performance between the two, I would still want the production version of the DDL. Something tells me one is not entirely like the other.
If not DDL, then execution plans at the very least for both the faster and slower environments.
the problem is the tables i deal with are more than 7 million records...so i am not able to get the DDL's so quickly...
is it possible to get the DDL's based on our own counts needed?
i don't find any filter condition to specify about how many records of DDL's that i actually need...
DDL = data definition language
It's the CREATE statements for the tables, indexes, views et cetera that we need.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2012 at 6:00 am
Koen Verbeeck (3/23/2012)
Charmer (3/23/2012)
SQLRNNR (3/22/2012)
Lynn Pettis (3/22/2012)
What do you test your code on during development? You don't have a development or test database to work with?DDL = data definition language
It's the CREATE statements for the tables, indexes, views et cetera that we need.
Sorry Koen...
I misunderstood....
Here is my DDL...
Thanks,
Charmer
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply