Can I do this faster? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can I do this faster?

Author

Topic

krsk
Starting Member

21 Posts

Posted - 2003-03-27 : 04:03:22

Hi :-)

I'am a bit new to this especially in regard to optimization...

How can I make this SQl statement perform better?

More indexes? Syntax?

I left join because I need to make sure that no columns from A are missing. Except for the AccessTable in the nested select which is supposed to limit the A. (according to user rights)

CREATE VIEW dbo.VW_BALI_AFS_UGE_0
AS
SELECT d.kategori,
a.kat,
a.ANTAL,
a.PRIS_ENGANG,
a.PRIS_LOEBENDE,
a.NIV1NAVN,
a.NIV2NAVN,
a.NIV3NAVN,
a.NIV4NAVN,
a.CU_PRODUKT_TEKST,
a.PRODUKT_NUMMER,
a.PRODUKT_GRP,
a.PRODUKT_ELEMENT,
a.PRODUKT_FUNKTION,
a.INTERN_EKSTERN_MARK,
a.ENHED,
a.KUNDESEGMENT_COLUMBUS_KIS,
a.FORHANDLER,
a.TJENESTE,
a.SERVICE_PROVIDER_KODE,
c.service_prov_name as service_provider_navn,
a.INDTASTENDE_ORG,
a.ORG_NIVEAU1,
a.ORG_NIVEAU2,
a.ORG_NIVEAU3,
a.ORG_NIVEAU4,
a.ORG_NIVEAU5,
a.ORG_NIVEAU6,
a.AFSAETNING_KODE,
a.KUNDESAGS_KODE,
a.TRANSKODE,
a.SAERGRUPPE_MARK_NY,
a.SAERGRUPPE_MARK_GL,
a.ORDRE_DATO,
a.PERIODE,
a.FRA_XFELT_NY,
a.FRA_XFELT_GL,
a.TIL_XFELT_NY,
a.TIL_XFELT_GL,

A.forvalgs_kode_indland,
A.forvalgs_kode_udland,

forvalgs_navn_udland =
CASE b.oper_prefix_code
WHEN a.forvalgs_kode_udland THEN b.oper_name
ELSE ''
End,
forvalgs_navn_indland =
CASE b.oper_prefix_code
WHEN a.forvalgs_kode_indland THEN b.oper_name
ELSE ''
End

FROM dbo.BALI_AFS_UGE_0 A
LEFT JOIN dbo.bali_r3502_ref_forvalg B ON (A.forvalgs_kode_indland = B.oper_prefix_code OR A.forvalgs_kode_udland = B.oper_prefix_code)
AND b.start_date <= (convert(varchar,getdate(),101))
AND
(
B.end_date > (convert(varchar,getdate(),101))
OR
B.end_date is null
)
LEFT JOIN dbo.bali_r3502_ref_serv_prod C ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D ON A.kat=D.kat
WHERE (UPPER(A.ENHED) IN
(SELECT UPPER(authValue) AS enhed
FROM dbo.RowAccessControl
WHERE userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND startDate <= getDate()
AND (endDate > getdate() OR endDate IS NULL)))

All tables but A are very small -100 rows
Table A has the following index's:
IDX_NC_KAT
IDX_NC_NIVNAVN1
IDX_NC_NIVNAVN2
Table A has 370.000 rows

Any suggestion will be welcome! :-)

/Kristian

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-27 : 05:30:10

For starters, you could change the IN clause to another INNER join...

quote:

LEFT JOIN dbo.bali_r3502_ref_serv_prod C ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D ON A.kat=D.kat
WHERE (UPPER(A.ENHED) IN
(SELECT UPPER(authValue) AS enhed
FROM dbo.RowAccessControl
WHERE userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND startDate <= getDate()
AND (endDate > getdate() OR endDate IS NULL)))

to

LEFT JOIN dbo.bali_r3502_ref_serv_prod C ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D ON A.kat=D.kat
LEFT JOIN dbo.RowAccessControl ON
UPPER(A.ENHED) = UPPER(authValue)
AND userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND startDate <= getDate()
AND (endDate > getdate() OR endDate IS NULL))

OS

krsk
Starting Member

21 Posts

Posted - 2003-03-27 : 06:22:28

mohdowais:

Why would that be faster? I tested this - isolated without the other joins and only joining the accesstabel then the nested select is faster...Can you explain?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-27 : 07:53:41

Two thoughts, note sure if any will help:

quote:

LEFT JOIN dbo.bali_r3502_ref_forvalg B ON (A.forvalgs_kode_indland = B.oper_prefix_code OR A.forvalgs_kode_udland = B.oper_prefix_code)

you might be better off just joining the bali_r3502_ref_forvalg table twice -- once by each code, instead of once with an OR. Not sure about that, but it may be worth looking to.

quote:

UPPER(A.ENHED) IN
(SELECT UPPER(authValue)

Is your database case-sensitive? the UPPER() function make stop you from being able to use indexes in this case. If it is not case sensitive, get rid of the UPPER() function. If it is, I would convert the data to keep it consistent in both tables if possible. This could be causing some definite performance problems.

Other than that, it looks fine to me ... how does it perform? what kind of improvement are you looking for?

oh... and why :

quote:

AND b.start_date <= (convert(varchar,getdate(),101))
AND
(
B.end_date > (convert(varchar,getdate(),101))

why are you converting the dates to a varchar() ? to get rid of the time, I suppose? I guess this should be fine, it should only do the conversion once not for every row...

- Jeff

krsk
Starting Member

21 Posts

Posted - 2003-03-27 : 08:35:20

Hi :-)

I've changed the statement to:

FROM
(
select * from dbo.BALI_AFS_UGE_0
WHERE ENHED IN
(SELECT authValue AS enhed
FROM dbo.RowAccessControl
WHERE userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND GetDate() between StartDate and IsNull(EndDate, '20491231'))
) A
LEFT JOIN dbo.bali_r3502_ref_forvalg B ON (A.forvalgs_kode_indland = B.oper_prefix_code OR A.forvalgs_kode_udland = B.oper_prefix_code)
AND GetDate() between b.start_date and IsNull(b.End_Date, '20491231')
LEFT JOIN dbo.bali_r3502_ref_serv_prod C ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D ON A.kat=D.kat

It seems to running a bit faster but not much -> 54 sec for a select * with no where clause. Gained about 3 sec.

I'am not really sure how much faster it should be - i'am just trying to make it as fast as possible.

/Kristian :-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-27 : 09:28:26

Are you sure the WHERE IN () is faster than an INNER JOIN?

select * from dbo.BALI_AFS_UGE_0
WHERE ENHED IN
(SELECT authValue AS enhed
FROM dbo.RowAccessControl
WHERE userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND GetDate() between StartDate and IsNull(EndDate, '20491231'))

vs.

select * from dbo.BALI_AFS_UGE_0
INNER JOIN
RowAccessControl
ON
enhed = authValue
WHERE
userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND GetDate() between StartDate and IsNull(EndDate, '20491231'))

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-27 : 12:49:47

Well,

Here's my entry for the contest.

How may rows would there be in a results between table A and Table E, that would be important to know. If it's a lot, it's a lot, BUT....

Anyway, here's my Entry. Hope it helps:
SELECT *
FROM dbo.BALI_AFS_UGE_0 A
LEFT JOIN dbo.bali_r3502_ref_forvalg B
ON B.oper_prefix_code In(A.forvalgs_kode_udland, A.forvalgs_kode_indland)
AND GetDate() BETWEEN start_date AND IsNull(end_date,GetDate())
LEFT JOIN dbo.bali_r3502_ref_serv_prod C
ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D
ON A.kat=D.kat
WHERE EXISTS (SELECT 1 FROM dbo.RowAccessControl E
WHERE A.ENHED = E.authValue
AND userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND GetDate() BETWEEN start_date AND IsNull(end_date,GetDate()))

Brett

8-)

krsk
Starting Member

21 Posts

Posted - 2003-03-28 : 05:11:36

I've tested >jsmith8858< versus situation:

The inner join ran for 53 sec.

The nested select ran for 39 sec.

So a clear winner here :-)

****

Brett:

It is a accesslimiting table so it varies, but when I run it it actualy returns all the rows from A - So in principal when i run the query I could leave that join out.

Maybe a test like that could be worth while?

In this case it returns 370.000 rows.

I tested your suggestion - and it was much faster, but unfortunatly i left out 350.000 rows :-) This might be because of your select 1 (It will in most cases return about 3-4 rows) The only table that may limit A i the accesstable...

Thank you all of you for the ideas! But it seems that 53 sec is the best it can do :-)

(A hidden challenge :-)

Edited by - krsk on 03/28/2003 05:12:50

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-28 : 09:31:05

I B Confussed?

That part of your statement:

quote:

FROM dbo.BALI_AFS_UGE_0 A
LEFT JOIN dbo.bali_r3502_ref_forvalg B ON (A.forvalgs_kode_indland = B.oper_prefix_code OR A.forvalgs_kode_udland = B.oper_prefix_code)
AND b.start_date <= (convert(varchar,getdate(),101))
AND
(
B.end_date > (convert(varchar,getdate(),101))
OR
B.end_date is null
)
LEFT JOIN dbo.bali_r3502_ref_serv_prod C ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D ON A.kat=D.kat

WHERE (UPPER(A.ENHED) IN (SELECT UPPER(authValue) AS enhed
FROM dbo.RowAccessControl
WHERE userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND startDate <= getDate()
AND (endDate > getdate() OR endDate IS NULL)))

Should do the same as my SELECT...EXISTS. What I am doing is limiting table A to just the rows that qualify for your IN statement. You're saying it works differently?

Did I say I was (Dazed and) Confussed (For so long it isn't true)?

With your In, I believe he has to Scan A for every row, but for the existance he (the optimizer) will only use the rows the are true, and then attempt the left join(s).

How about this (although it's along the same lines as what I already have..). Please let me know why you don't want to limit A by D, also:

I'm very curious (also dazed....ect). Try this:

SELECT *
FROM ( SELECT * FROM dbo.BALI_AFS_UGE_0 X
WHERE EXISTS (SELECT 1 FROM dbo.RowAccessControl E
WHERE X.ENHED = E.authValue
AND userId = DBO.getSuser_Sname()
AND systemId = 'BALI'
AND GetDate() BETWEEN start_date AND IsNull(end_date,GetDate()))
) As A
LEFT JOIN dbo.bali_r3502_ref_forvalg B
ON B.oper_prefix_code In(A.forvalgs_kode_udland, A.forvalgs_kode_indland)
AND GetDate() BETWEEN start_date AND IsNull(end_date,GetDate())
LEFT JOIN dbo.bali_r3502_ref_serv_prod C
ON A.service_provider_kode=C.source_system_code
LEFT JOIN dbo.bali_r3502_ref_kategori D
ON A.kat=D.kat

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources