Complex Queries

Hi..
Is there any way to re-write this query. The query is correct one, but i am not able to get the data faster.
I am combining the same qry with other qry(with different tables) using union.


select 'Description' LineDesc,
li.bldivi Division,
li.blfaci Facility,
li.blagnb AgreementNo,
li.blponr,
agh.bhagcn CustNo,
agh.bhacnm CustName,
agh.bhaotp house,
dwr.bmarcs Reason,
dwdesc.cttx40 DownReason,
dwli.stpnca Value,
dwli.stfvdt FromDate,
dwli.stlvdt ToDate,
fa.cffacn Name,
hl.stivno No,
hl.stivdt InvDate,
xt.tltx60 Text
From
stagli li,
staghe agh,
staghd dwli,
stagdn dwr,
csytab dwdesc,
cfacil fa,
staghl hl,
ssytxl xt,
(select stagnb, stponr, max(strgdt + strgtm) datetime from staghl
group by stagnb, stponr) iv
Where li.blcono = agh.bhcono
and li.blagnb = agh.bhagnb
and li.bldivi = agh.bhdivi
and li.blfaci = agh.bhfaci
and agh.bhcono = 1
and dwli.stitds = 'down period'
and dwli.stcono = li.blcono
and dwli.stdivi = li.bldivi
and dwli.stfaci = li.blfaci
and dwli.stagnb = li.blagnb
and dwli.stponr = li.blponr
and dwr.bmcono = li.blcono
and dwr.bmdivi = li.bldivi
and dwr.bmfaci = li.blfaci
and dwr.bmagnb = li.blagnb
and dwr.bmponr = li.blponr
and dwdesc.ctstco = 'ARCC'
and dwdesc.ctstky = dwr.bmarcs
and dwdesc.ctcono = agh.bhcono
and fa.cfcono = agh.bhcono
and fa.cfdivi = agh.bhdivi
and fa.cffaci = agh.bhfaci
and hl.stcono = li.blcono
and hl.stagnb = li.blagnb
and hl.stponr = li.blponr
and (hl.strgdt+hl.strgtm) = iv.datetime
and hl.stagnb = iv.stagnb
and hl.stponr = iv.stponr
and xt.tlcono = li.blcono
and xt.tltxid = li.blpotx
and xt.tllino = 1
and li.blcono = 1




SURYA

Be pure to do good.[Vivekananda]

Use JOIN syntax. Check if the fields that you're joining on, and fields that you filter in, have adequate indexes.

Your derived table should be a correlated subquery in the WHERE part.

I have used join syntax only. All the field joining condition correct, indexes also there. The resulted output are approved one.

SURYA

Be pure to do good.[Vivekananda]

No, your query doesn't have JOIN syntax: you don't actually use the keyword JOIN with an ON clause. Keep up with the times, they are a-changing: no more joining in the WHERE clause please.

And I would still recommend changing the derived table into a correlated subquery in the WHERE clause.

You mean to say like this

inner join stagli on li.blcono = agh.bhcono and li.blagnb = agh.bhagnb and li.bldivi = agh.bhdivi and li.blfaci = agh.bhfaci
inner join staghd on dwli.stcono = li.blcono and dwli.stdivi = li.bldivi and dwli.stfaci = li.blfaci and dwli.stagnb = li.blagnb and dwli.stponr = li.blponr
inner join stagdn on dwr.bmcono = li.blcono and dwr.bmdivi = li.bldivi and dwr.bmfaci = li.blfaci and dwr.bmagnb = li.blagnb and dwr.bmponr = li.blponr



SURYA

Be pure to do good.[Vivekananda]

Could you rewrite that as a complete FROM clause with the aliases in place?

Also, run the query in Query Analyzer to see if SQL can make heads-and-tails of it.

Look for the execution plans and check which indexes are being used and if you find any Scan then modify the data access method by modifying where clause to have Index Seek.
Also as suggested by adriaan, have indexes on joining columns.

I have done the same, the performance the qry is increases little bit.

------------------------------------------

quote:Originally posted by Adriaan

Could you rewrite that as a complete FROM clause with the aliases in place?

Also, run the query in Query Analyzer to see if SQL can make heads-and-tails of it.

SURYA

Be pure to do good.[Vivekananda]

quote:Originally posted by ranjitjain

Look for the execution plans and check which indexes are being used and if you find any Scan then modify the data access method by modifying where clause to have Index Seek.
Also as suggested by adriaan, have indexes on joining columns.

-------
I have cheked execution plans and indexes on joining columns. the qry is ok with that.

The possible reasons are, the data is more(crores) in all tables and joining more tables.
executing one more qry using union command.

Can i insert the data in temp tables ?

SURYA

Be pure to do good.[Vivekananda]

If you are using UnION in your select then you can get further imrovement by replacing it with UION ALL as it returns result much faster as it excludes the check for duplicates.
Use UION ALL only if you are sure and dont want to display any duplicates.

You can even try using temp tables. But if the data in table is huge and you are just working with piece of data then only go for it.
Else you will have to again create index on temp table.
But if the rows which you are going to insert in temp table is less then you can even check for table variables as it offers more performance than temp tables.

You can experiment with all of this.
Run your query by adding this at top and check in messages pane to find the read count whether any improvement or not

SET STATISTICS IO ON

Inserting into temp table will not increase the performance.
Try to use subquery for those tables which is having less records.
or
You can try the below

(select fa.cffacn Name from cfacil fa With (NOLOCK) where ........)
(Select tltx60 Text from ssytxl xt With (NOLOCK) where .....)
--Try to use subquery for staghl table as well
use " With (Nolock) " if you have 10 millions of rows



Surendra Kalekar

quote:Originally posted by surendrakalekar

Inserting into temp table will not increase the performance.
Try to use subquery for those tables which is having less records.
or
You can try the below

(select fa.cffacn Name from cfacil fa With (NOLOCK) where ........)
(Select tltx60 Text from ssytxl xt With (NOLOCK) where .....)
--Try to use subquery for staghl table as well
use " With (Nolock) " if you have 10 millions of rows



Surendra Kalekar

----------------------
i wll try this and let u know..

SURYA

Be pure to do good.[Vivekananda]

Hi Ranjit,
I tried all with this, Now the Qry returning the output faster compartive earlier.
->Stored the required information in hash table.(minimum req data in large table)
->joining the hash table with other ones.
->Used union all , the data is getting without duplicates, but have to chk future data.

I am trying to get the otherway what Surendra Kalekar suggested...




SURYA

You can also read about using table variable which is more faster than creating and dropping and using #temptables as for table type if data is very less than SQL holds it in memory and does the processing

quote:Originally posted by ranjitjain

You can also read about using table variable which is more faster than creating and dropping and using #temptables as for table type if data is very less than SQL holds it in memory and does the processing

Depends on the amount of data you will be putting into the table variable. And don't forget that you can add indexes to temp tables, not to table variables ...

Thanks you all.

I have prepared set of queries and sending for approval.Getting the data faster, after posting the qry..

Included all possible conditions what we have discussed...

01. using temp tables (hash tables),indexes on hash tables,
02. using subqueries for those tables which is having less records.

Once again Thanks to Adriaan,dineshasanka,Ranjit jain,Surendra Kalekar





SURYA

Good to know that after discussing your problem here,<br />You finally made to have fast running Procedure.[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />

Hi Surya,
Can you post your modified code?


Surendra Kalekar

Once it is approved i wll post it.
Thanks..

quote:Originally posted by surendrakalekar

Hi Surya,
Can you post your modified code?


Surendra Kalekar

SURYA

quote:Originally posted by Madhivanan

>>And don't forget that you can add indexes to temp tables, not to table variables ...

I hope you meant this

Alter table @t............

Madhivanan

Failing to plan is Planning to fail

No, I meant this:

CREATE TABLE #TMP (Col1 INT)
CREATE CLUSTERED INDEX ixTMP ON #TMP (Col1)

You can do anything with a temporary table that you can also do with regular user table. You can have a PK, indexes and (default) constraints. I tried triggers, but they're not allowed on temporary objects. Haven't tried relationships - probably not allowed either, perhaps FK is supported. Does anyone know?

If you try creating an index on a table variable, you get a syntax error - like this:

DECLARE @TMP TABLE (Col1 INT)
CREATE CLUSTERED INDEX ixTMP ON @TMP (Col1)
---
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@TMP'.

Thanks for pointing out the PK option in the table variable. YOu can add DEFAULT constraints on columns as well, like this:
DECLARE @TMP TABLE (Col1 INT PRIMARY KEY CLUSTERED, Col2 VARCHAR(10) DEFAULT(''))

Another point of interest ... A table variable will be created as a temporary table, if SQL Server estimates that the amount of data to be inserted into the table will exceed a certain threshold.

Thanks madhivanan
i did samething only.

quote:Originally posted by Madhivanan

>>And don't forget that you can add indexes to temp tables, not to table variables ...

I hope you meant this

Alter table @t............

Madhivanan

Failing to plan is Planning to fail

SURYA

Good point Madhivanan,
I never tried it and never thought of it but really a good one to keep that in my mind for future.