| Author |
 Topic  |
|
|
Zanph
Starting Member
12 Posts |
 Posted - 08/12/2008 : 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
Flowing Fount of Yak Knowledge
USA
7174 Posts |
Posted - 08/12/2008 : 10:03:07
|
| Did you check Execution plan and high cost? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30121 Posts |
Posted - 08/12/2008 : 10:03:51
|
Do you have proper indexes?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 08/12/2008 : 10:06:33
|
you're not using SQL Server are you?
Em |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 08/12/2008 : 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 |
Edited by - elancaster on 08/12/2008 10:11:34 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30121 Posts |
Posted - 08/12/2008 : 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 - 08/13/2008 : 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 |
Edited by - Zanph on 08/13/2008 02:26:16 |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 08/13/2008 : 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 - 08/13/2008 : 02:53:51
|
Sry I mean MS SQL SERVER 2000, I just wrote it false
Philipp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30121 Posts |
Posted - 08/13/2008 : 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 - 08/13/2008 : 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
Sweden
30121 Posts |
Posted - 08/13/2008 : 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
Sweden
30121 Posts |
Posted - 08/13/2008 : 03:15:59
|
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" |
|
|
|
Zanph
Starting Member
12 Posts |
Posted - 08/13/2008 : 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;
Thank you all for the help
Topic solved 
I wish you a nice day
Philipp |
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 08/13/2008 : 04:39:39
|
so you're still using LIMIT...? so you're NOT using SQL Server
Em |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30121 Posts |
Posted - 08/13/2008 : 04:41:52
|
See post made 08/13/2008 : 02:59:07
E 12°55'05.25"
N 56°04'39.16" |
|
|
| |
Topic |
|
|
|
LEFT JOIN really slow
