LEFT JOIN really slow - 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)

LEFT JOIN really slow

Author

Topic

Zanph
Starting Member

12 Posts

Posted - 2008-08-12 : 09:57:49

If i make the following query i always have to reboot the server. So it is a little problem^^. Can someone pls look over where the mistake could be.
In gzdaten are abizt 8000 entries, diaeten 60 entries, gzprefix 80 entries and in all other one less then 20.


SELECT
     org.*,
     mitarbeiterstamm.*,
     gzprefix.PREBEZ,
     gzprefix.SORT_ID,
     gzdaten.GZ,
     gzdaten.GZPOST,
     org_arbcodes.*,
     org_qualif.*,
     ..... (i cut down this list for a better overview)
    FROM
     kilometersaetze, gzprefix, tarif_ma, gzdaten, mitarbeiterstamm, org
     LEFT JOIN org_matcodes ON (org.MATCODE = org_matcodes.CODE)
     LEFT JOIN fahrzeuge ON (org.KFZCODE = fahrzeuge.FAHRZEUGID )
     LEFT JOIN material_saetze ON (material_saetze.MATERIAL_ID = org_matcodes.ID)
     LEFT JOIN org_barcodes ON (org.BARCODE = org_barcodes.CODE),
     org_arbcodes LEFT JOIN org_qualif ON (org_qualif.CODE = org_arbcodes.QUAL),
     diaeten LEFT JOIN diaetensaetze ON (diaeten.BINCODE = diaetensaetze.BINCODE)
    WHERE
     org.DATUM BETWEEN kilometersaetze.VON AND kilometersaetze.BIS AND
     gzprefix.PREBEZ = "'FIS'" AND
     gzdaten.GZ = "'0" AND
     (gzdaten.GZPOST IS NULL OR gzdaten.GZPOST = "") AND                                    
     gzdaten.GZPRE = gzprefix.PRENR AND
     org.GZID = gzdaten.GZID AND
     org.DATUM BETWEEN '2008-03-12' AND '2008-08-12'   AND
     org.PERSONALID = mitarbeiterstamm.PERSONALNR AND
     org.DATUM BETWEEN tarif_ma.VON AND tarif_ma.BIS AND
     org.ARBCODE = org_arbcodes.CODE AND
     org.DIAETCODE = diaeten.CODE
     ORDER BY org.DATUM
     LIMIT 1000;

I already made a topic but i thought this is another problem.

Thank you in forward for the help
Philipp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 10:03:07

Did you check Execution plan and high cost?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 10:03:51

Do you have proper indexes?

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-12 : 10:06:33

you're not using SQL Server are you?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-12 : 10:10:02

quote:

FROM
kilometersaetze, gzprefix, tarif_ma, gzdaten, mitarbeiterstamm, org
LEFT JOIN org_matcodes ON (org.MATCODE = org_matcodes.CODE)
LEFT JOIN fahrzeuge ON (org.KFZCODE = fahrzeuge.FAHRZEUGID )
LEFT JOIN material_saetze ON (material_saetze.MATERIAL_ID = org_matcodes.ID)
LEFT JOIN org_barcodes ON (org.BARCODE = org_barcodes.CODE),
org_arbcodes LEFT JOIN org_qualif ON (org_qualif.CODE = org_arbcodes.QUAL),
diaeten LEFT JOIN diaetensaetze ON (diaeten.BINCODE = diaetensaetze.BINCODE)

...how do these join together?

EDIT - oh, they're in the where clause. yuk

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 10:21:17

He's using MySQL, by the look of "LIMIT 1000;" clause.

E 12°55'05.25"
N 56°04'39.16"

Zanph
Starting Member

12 Posts

Posted - 2008-08-13 : 02:19:23

thanks for the replies
i use MYSQL Server 2000

@ sodeep
Did you check Execution plan and high cost?
No I haven't, I don't know what this is, can I make this like a normal query, because I maybe not allowed to go to the server (it is my summerjob).

@ Peso
Do you have proper indexes?
Yes, every table has a unique id. They are named, *CODE or *ID

If I make the following query it works (but I need both combinations :( )


SELECT
     org.*,
     mitarbeiterstamm.*,
     gzprefix.PREBEZ,
     gzprefix.SORT_ID,
     gzdaten.GZ,
     gzdaten.GZPOST,
     org_arbcodes.*,
     org_qualif.*,
     ..... (i cut down this list for a better overview)
    FROM
     kilometersaetze, gzprefix, tarif_ma, gzdaten, mitarbeiterstamm, org
     LEFT JOIN org_matcodes ON (org.MATCODE = org_matcodes.CODE)
     LEFT JOIN fahrzeuge ON (org.KFZCODE = fahrzeuge.FAHRZEUGID )
     LEFT JOIN material_saetze ON (material_saetze.MATERIAL_ID = org_matcodes.ID)
     LEFT JOIN org_barcodes ON (org.BARCODE = org_barcodes.CODE),
     org_arbcodes LEFT JOIN org_qualif ON (org_qualif.CODE = org_arbcodes.QUAL),
     diaeten LEFT JOIN diaetensaetze ON (diaeten.BINCODE = diaetensaetze.BINCODE)
    WHERE
     org.DATUM BETWEEN kilometersaetze.VON AND kilometersaetze.BIS AND
     gzprefix.PREBEZ = "ISC" AND
     gzdaten.GZ = "08011" AND
     gzdaten.GZPOST = "00" AND          //this is a query which i use to find projects were the GZPOST is set
     gzdaten.GZPOST IS NULL OR gzdaten.GZPOST = "") AND 
     gzdaten.GZPRE = gzprefix.PRENR AND
     org.GZID = gzdaten.GZID AND
     org.DATUM BETWEEN '2008-03-12' AND '2008-08-12'   AND
     org.PERSONALID = mitarbeiterstamm.PERSONALNR AND
     org.DATUM BETWEEN tarif_ma.VON AND tarif_ma.BIS AND
     org.ARBCODE = org_arbcodes.CODE AND
     org.DIAETCODE = diaeten.CODE
     ORDER BY org.DATUM
     LIMIT 1000;

Sry that I forgot important information.
Philipp

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 02:48:41

"proper indexes" does not just mean a unique ID. and given that you are using MySQL, perhaps you would be better advised to post on a MySQL Forum. This site is dedicated to Microsoft SQL Server

Em

Zanph
Starting Member

12 Posts

Posted - 2008-08-13 : 02:53:51

Sry I mean MS SQL SERVER 2000, I just wrote it false

Philipp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 02:56:18

Still, Microsoft SQL Server does not support the last line of your query, the "LIMIT 1000;".
Thus we think you are using MySQL database on a Microsoft Windows 2000 Server.

E 12°55'05.25"
N 56°04'39.16"

Zanph
Starting Member

12 Posts

Posted - 2008-08-13 : 02:59:07

Oh ok, em, I had a look (or asked). Finally it is like you said Peso. Sry for the trouble.
But can you find a mistake in the query?

Philipp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:08:42

The query is really really slow because you return ALL columns in almost ALL tables by using * for column name.
So you will have a lot of table scans. You cannot use any index with this approach.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:15:59

[code]SELECT TOP 1000 org.*,
mitarbeiterstamm.*,
gzprefix.PREBEZ,
gzprefix.SORT_ID,
gzdaten.GZ,
gzdaten.GZPOST,
org_arbcodes.*,
org_qualif.*,
FROM gzprefix
INNER JOIN gzdaten ON gzdaten.GZPRE = gzprefix.PRENR
AND gzdaten.GZ = '0'
AND (gzdaten.GZPOST IS NULL OR gzdaten.GZPOST = '')
INNER JOIN org ON org.GZID = gzdaten.GZID
AND org.DATUM BETWEEN '2008-03-12' AND '2008-08-12'
INNER JOIN kilometersaetze ON org.DATUM BETWEEN kilometersaetze.VON AND kilometersaetze.BIS
INNER JOIN tarif_ma ON org.DATUM BETWEEN tarif_ma.VON AND tarif_ma.BIS
INNER JOIN mitarbeiterstamm on mitarbeiterstamm.PERSONALNR = org.PERSONALID
INNER JOIN org_arbcodes ON org_arbcodes.CODE = org.ARBCODE
LEFT JOIN org_matcodes ON org_matcodes.CODE = org.MATCODE
LEFT JOIN fahrzeuge ON fahrzeuge.FAHRZEUGID = org.KFZCODE
LEFT JOIN material_saetze ON material_saetze.MATERIAL_ID = org_matcodes.ID
LEFT JOIN org_barcodes ON org_barcodes.CODE = org.BARCODE
INNER JOIN diaeten ON diaeten.CODE = org.DIAETCODE
LEFT JOIN org_qualif ON org_qualif.CODE = org_arbcodes.QUAL
LEFT JOIN diaetensaetze ON diaetensaetze.BINCODE = diaeten.BINCODE
WHERE gzprefix.PREBEZ = 'FIS'
ORDER BY org.DATUM[/code]

E 12°55'05.25"
N 56°04'39.16"

Zanph
Starting Member

12 Posts

Posted - 2008-08-13 : 04:37:18

Thank you very much for the answer but i have found another way to solve this problem.


SELECT
   org_arbcodes.BEZ,
   org_matcodes.BEZ,
   diaeten.BEZ,
   fahrzeuge.BEZ,
   diaetensaetze.BETRAG,
   tarif_ma.BETRAG,
   org_barcodes.BEZ,
   org.PERSONALID,
   org.DATUM,
   org.ENDE,
   org.ARBCODE,
   org.ANMERKUNG,
   org.DIAETCODE,
   org.MATCODE,
   org.MATMENGE,
   org.KFZCODE,
   org.KMGEFAHR,
   org.BARCODE,
   org.BARBETRAG,
   material_saetze.WERT,
   kilometersaetze.SATZ,
   diaetensaetze.KOMMENTAR,
   mitarbeiterstamm.VORNAME,
   mitarbeiterstamm.NACHNAME,
   mitarbeiterstamm.PERSONALID,
   mitarbeiterstamm.QUALIFIKATION,
   gzprefix.PREBEZ,
   gzprefix.SORT_ID,
   gzdaten.GZ,
   gzdaten.GZPOST,
   org_arbcodes.*,
   org_qualif.*
FROM kilometersaetze, gzprefix, tarif_ma, gzdaten, mitarbeiterstamm, org
   LEFT JOIN org_matcodes ON (org.MATCODE = org_matcodes.CODE)
   LEFT JOIN fahrzeuge ON (org.KFZCODE = fahrzeuge.FAHRZEUGID )
   LEFT JOIN material_saetze ON (material_saetze.MATERIAL_ID = org_matcodes.ID)
   LEFT JOIN org_barcodes ON (org.BARCODE = org_barcodes.CODE), org_arbcodes
   LEFT JOIN org_qualif ON (org_qualif.CODE = org_arbcodes.QUAL),
   diaeten LEFT JOIN diaetensaetze ON (diaeten.BINCODE = diaetensaetze.BINCODE) WHERE org.DATUM BETWEEN kilometersaetze.VON AND    kilometersaetze.BIS AND 
   gzprefix.PREBEZ = "ISC" AND
   gzdaten.GZ = "0" AND
   (gzdaten.GZPOST IS NULL OR gzdaten.GZPOST = "") AND // I let this out and now it works (I just have to wait about 1 secound)
   gzdaten.GZPRE = gzprefix.PRENR AND
   org.GZID = gzdaten.GZID AND
   org.DATUM BETWEEN '2008-03-06' AND '2008-08-13' AND
   org.PERSONALID = mitarbeiterstamm.PERSONALNR AND
   org.DATUM BETWEEN tarif_ma.VON AND
   tarif_ma.BIS AND
   org.ARBCODE = org_arbcodes.CODE AND
   org.DIAETCODE = diaeten.CODE ORDER BY org.DATUM
LIMIT 100;