Access to T-SQL - 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)

Access to T-SQL

Author

Topic

Willio
Starting Member

20 Posts

Posted - 2006-05-15 : 10:48:41

Hello,

I come here because of a problem in Access that i want to solve in SQL.

I have a table with some information about distance from home to work, prices, extras etc. Now there is a query that makes a calculation in Access with results are used in an other query.

In the end it takes 6 secondes to do the math. And that's to long. My idea was to put it al in SQL and make a view of it so that Access doesn't need to calculate any more.

The problem seems to be solved when i remove the groep by function, but still i want the total of a project and so need te group by.

In total there are 3 queries working toghether. The first 2 are fast enough, but the third is slow.

Any thoughts?

Thanks Willio

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-15 : 11:24:57

Make the third query more efficient maybe?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Willio
Starting Member

20 Posts

Posted - 2006-05-16 : 02:59:02

Here you can that a look at the three queries, i think they slow down eachother.

Query 1 does the calculation of the periode: Q_Uur_tarief_Bereken

SELECT [Periode].[PeriodeID], (([basissalaris]/[uren])*40*12.96*1.23)/1700 AS Expr1, IIf([km]-50>0,[km]-50,0) AS Expr8, IIf([km]<50,[km],50) AS Expr7, IIf([km]>50,20*4.31,IIf([km]>30,([km]-30)*4.31,0)) AS Expr4, [expr4]*0.6 AS Expr5, IIf([km]=0,0,IIf([km]<50,[expr6]/(2*[km]*21),[expr6]/(2*50*21))) AS Expr3, IIf([km]<10,0,IIf([km]<15,65,IIf([km]<20,91,130))) AS Expr2, IIf([km]=0,0,([expr3]*[expr7]+[expr8]*0.16)/[km]) AS Expr9, [expr9]*[km]*2/8 AS Expr10, [expr2]+[expr5] AS Expr6, [expr1]+[expr10]+([detachtegemoetk]/8)+[extrapmaand]/(21*8) AS Expr11, IIf([kostprijsinhuur]>0,[tarief]-([kostprijsinhuur]*[uren]/[urenvergoed]),[tarief]-([expr11]*[uren]/[urenvergoed])) AS Expr12, [Periode].[KostprijsInhuur], IIf([expr11]=0,[kostprijsinhuur],[expr11]) AS Allin
FROM Periode;

Query 2 does the calculation by looking at the working hours and is using the allin field of query 1 : Q_uur_tarief

SELECT Project.ProjectID, UrenBriefje.Uren_Slash, Medewerkers.Achternaam, UrenBriefje.Weeknr, [Ma_N]+[Ma_O]+[Di_N]+[Di_O]+[Wo_N]+[Wo_O]+[Do_N]+[Do_O]+[Vr_N]+[Vr_O]+[Za_O]+[Zo_O] AS weekuren, Q_Uur_tarief_Bereken.Allin AS Tarief, [tarief]*[weekuren] AS week_totaal
FROM (((Medewerkers INNER JOIN Periode ON Medewerkers.MedewerkerID = Periode.MedewerkerID) INNER JOIN UrenBriefje ON (Medewerkers.MedewerkerID = UrenBriefje.MedewerkerID) AND (Periode.PeriodeID = UrenBriefje.PeriodeID)) INNER JOIN Project ON UrenBriefje.ProjectID = Project.ProjectID) INNER JOIN Q_Uur_tarief_Bereken ON Periode.PeriodeID = Q_Uur_tarief_Bereken.PeriodeID;

Finaly its being grouped to attach the calculation to a project.

SELECT Project.ProjectID, Sum(Q_Uur_Tarief.weekuren) AS Uren_Project_Totaal, Sum(Q_Uur_Tarief.week_totaal) AS Uren_Kosten_Totaal
FROM Q_Uur_Tarief INNER JOIN Project ON Q_Uur_Tarief.ProjectID = Project.ProjectID
GROUP BY Project.ProjectID;

The Table periode gives data about the things of a person in at some moment.
The Table Medewerkers are the people working
The Table Urenbriefjes is full of hours worked, contains 13000 record at this moment
The Table project has the data about projects

Willio
Starting Member

20 Posts

Posted - 2006-05-16 : 04:05:41

Okey, problem changed!

I tested some more and the problem is in the many joins, because of the "double" join by using 3 queries the first query is executed many many times. Because the total of the first one should never change a made a new field in the table periode. I calculate it ones and at the outcoming to the table. At that point the first query is no longer needed. BUT if something chances in the table periode i should need to execute the query so that the values are changed.

I think the answer is the following. Create a table called Periode_Allin with two fields, PeriodeID and Allin. Redesign the first query in a way that it fills the new table. Create a trigger and let the table refill every 5? minutes.

Question now is how to redesign the query :). Off course i started with that allready but i am stuck when i like to use other fields to calculate further.

SELECT PeriodeID, ((Basissalaris/uren)*40*12.96*1.23)/1700 AS Expr1, (CASE When (km-50) > 0 Then (km-50) Else 0 End) AS Expr8, (Expr7*Expr8) As Expr9 FROM Periode.

Expr7 * Expr8 is not excepted....Why not???

Wow what a story, and all in English :D
Thanks!!
Willio

Willio
Starting Member

20 Posts

Posted - 2006-05-17 : 02:55:16

Problems solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources