SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 LEFT JOIN really slow		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zanph
Starting Member

12 Posts
Posted - 08/12/2008 :  09:57:49  Show Profile  Visit Zanph's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts
Posted - 08/12/2008 :  10:03:07  Show Profile  Reply with Quote
Did you check Execution plan and high cost?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30292 Posts
Posted - 08/12/2008 :  10:03:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do you have proper indexes?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts
Posted - 08/12/2008 :  10:06:33  Show Profile  Reply with Quote
you're not using SQL Server are you?

Em
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts
Posted - 08/12/2008 :  10:10:02  Show Profile  Reply with Quote
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
Edited by - elancaster on 08/12/2008 10:11:34
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30292 Posts
Posted - 08/12/2008 :  10:21:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
He's using MySQL, by the look of "LIMIT 1000;" clause.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Zanph
Starting Member

12 Posts
Posted - 08/13/2008 :  02:19:23  Show Profile  Visit Zanph's Homepage  Reply with Quote
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
Edited by - Zanph on 08/13/2008 02:26:16
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts
Posted - 08/13/2008 :  02:48:41  Show Profile  Reply with Quote
"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
Go to Top of Page

Zanph
Starting Member

12 Posts
Posted - 08/13/2008 :  02:53:51  Show Profile  Visit Zanph's Homepage  Reply with Quote
Sry I mean MS SQL SERVER 2000, I just wrote it false

Philipp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30292 Posts
Posted - 08/13/2008 :  02:56:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Zanph
Starting Member

12 Posts
Posted - 08/13/2008 :  02:59:07  Show Profile  Visit Zanph's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30292 Posts
Posted - 08/13/2008 :  03:08:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30292 Posts
Posted - 08/13/2008 :  03:15:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
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


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Zanph
Starting Member

12 Posts
Posted - 08/13/2008 :  04:37:18  Show Profile  Visit Zanph's Homepage  Reply with Quote
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;


Thank you all for the help
Topic solved

I wish you a nice day
Philipp
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts
Posted - 08/13/2008 :  04:39:39  Show Profile  Reply with Quote
so you're still using LIMIT...? so you're NOT using SQL Server

Em
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30292 Posts
Posted - 08/13/2008 :  04:41:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See post made 08/13/2008 : 02:59:07


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000