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.
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 helpPhilipp |
|
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. yukEm |
|
|
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 repliesi use MYSQL Server 2000@ sodeepDid 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). @ PesoDo 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 ServerEm |
|
|
Zanph
Starting Member
12 Posts |
Posted - 2008-08-13 : 02:53:51
|
Sry I mean MS SQL SERVER 2000, I just wrote it falsePhilipp |
|
|
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 gzprefixINNER 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.BISINNER JOIN tarif_ma ON org.DATUM BETWEEN tarif_ma.VON AND tarif_ma.BISINNER JOIN mitarbeiterstamm on mitarbeiterstamm.PERSONALNR = org.PERSONALIDINNER JOIN org_arbcodes ON org_arbcodes.CODE = org.ARBCODELEFT JOIN org_matcodes ON org_matcodes.CODE = org.MATCODELEFT JOIN fahrzeuge ON fahrzeuge.FAHRZEUGID = org.KFZCODELEFT JOIN material_saetze ON material_saetze.MATERIAL_ID = org_matcodes.IDLEFT JOIN org_barcodes ON org_barcodes.CODE = org.BARCODEINNER JOIN diaeten ON diaeten.CODE = org.DIAETCODELEFT JOIN org_qualif ON org_qualif.CODE = org_arbcodes.QUALLEFT JOIN diaetensaetze ON diaetensaetze.BINCODE = diaeten.BINCODEWHERE 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.DATUMLIMIT 100; Thank you all for the helpTopic solved I wish you a nice dayPhilipp |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-13 : 04:39:39
|
so you're still using LIMIT...? so you're NOT using SQL ServerEm |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-13 : 04:41:52
|
See post made 08/13/2008 : 02:59:07 E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|