SQL query slow from app

[Andreja Starcevic]

Hello,

I am using ADO to create some reports but it takes around 2 minutes to get results through my app and when executing query in MS SQL Studio i get results in 3 seconds?

When I was making some reports before I remember that using DECLARE vars at start of query speeds up a lot but even in this case it takes around 2 minutes to get this report.

I am using MS SQL EXPRESS server.

Best Regards

[David Field]

It does not make sense that using ADO would take so much longer.

I have noticed some difference in response but never more than a couple of seconds.

In order to further help you it would be necessary to know how you are defining your recordset and the query in question.

Regards,

David Field

[Andreja Starcevic]

David,

Thanks for response. I am posting a query and my function. 

Kind regards

[David Field]

A few tips that may help you.

oc:CommandTimeOut := 5 // or 10

Using the following can help to read easy your code:

#xcommand TEXT INTO <v> => #pragma __text|<v>+=%s+HB_EOL();IF <v> == NIL ; <v> := ""; END

TEXT INTO cSql

DECLARE @od_dat DATE = '%od_dat;

DECLARE @do_dat DATE = '%do_dat';

DECLARE @od_dat_p DATE = '%od_dat_p';

DECLARE @do_dat_p DATE = '%do_dat_p';

DECLARE @od_dat_pp DATE = '%od_dat_pp'; DECLARE @do_dat_pp DATE = '%do_dat_pp';

DECLARE @grupa CHAR(2) = '%grupa_var'; WITH memory_table AS ( SELECT OTPREM.kupac, SUM(OTPREM_s.kolicina * OTPREM_S.i_CENA) AS Promet2024T FROM REJ...OTPREM JOIN REJ...OTPREM_s ON OTPREM.veza = OTPREM_s.veza JOIN REJ...ROBA ON OTPREM_s.sifra_robe = ROBA.sifra WHERE OTPREM.DATUM BETWEEN @od_dat AND @do_dat AND ROBA.grupa = @grupa GROUP BY OTPREM.kupac ), memory_table1 AS ( SELECT OTPREM.kupac, SUM(OTPREM_s.kolicina * OTPREM_S.i_CENA) AS Promet2024P FROM REJ2024...OTPREM JOIN REJ2024...OTPREM_s ON OTPREM.veza = OTPREM_s.veza JOIN REJ2024...ROBA ON OTPREM_s.sifra_robe = ROBA.sifra WHERE OTPREM.DATUM BETWEEN @od_dat AND @do_dat AND ROBA.grupa = @grupa GROUP BY OTPREM.kupac ), memory_table2 AS ( SELECT OTPREM.kupac, SUM(OTPREM_s.kolicina * OTPREM_S.i_CENA) AS Promet2023 FROM REJ2023...OTPREM JOIN REJ2023...OTPREM_s ON OTPREM.veza = OTPREM_s.veza JOIN REJ2023...ROBA ON OTPREM_s.sifra_robe = ROBA.sifra WHERE OTPREM.DATUM BETWEEN @od_dat_p AND @do_dat_p AND ROBA.grupa = @grupa GROUP BY OTPREM.kupac ), memory_table3 AS ( SELECT OTPREM.kupac, SUM(OTPREM_s.kolicina * OTPREM_S.i_CENA) AS Promet2022 FROM REJ2022...OTPREM JOIN REJ2022...OTPREM_s ON OTPREM.veza = OTPREM_s.veza JOIN REJ2022...ROBA ON OTPREM_s.sifra_robe = ROBA.sifra WHERE OTPREM.DATUM BETWEEN @od_dat_pp AND @do_dat_pp AND ROBA.grupa = @grupa GROUP BY OTPREM.kupac ) SELECT KOMINT.sifra AS kupac, KOMINT.naziv1, (COALESCE(mt.Promet2024T, 0) + COALESCE(mt1.Promet2024P, 0)) AS Promet2024, COALESCE(mt2.Promet2023, 0) AS Promet2023, COALESCE(mt3.Promet2022, 0) AS Promet2022, (COALESCE(mt.Promet2024T, 0) + COALESCE(mt1.Promet2024P, 0)) - COALESCE(mt2.Promet2023, 0) AS Razlika FROM REJ...KOMINT KOMINT LEFT JOIN memory_table mt ON KOMINT.sifra = mt.kupac LEFT JOIN memory_table1 mt1 ON KOMINT.sifra = mt1.kupac LEFT JOIN memory_table2 mt2 ON KOMINT.sifra = mt2.kupac LEFT JOIN memory_table3 mt3 ON KOMINT.sifra = mt3.kupac ORDER BY Razlika;

ENDTEXT

// see hb_S · Petewg/harbour-core Wiki · GitHub for more info on this function, it is great!!!

cSql := hb_StrReplace( cSql, { "%od_da't" => od_dat, ;

                                                   "%do_dat" => do_dat,;

                                                   "%od_dat_p" => od_dat_p,;

                                                   "%do_dat_p" => do_dat_p,;

                                                   "%od_dat_pp" => od_dat_pp,;

                                                   "%do_dat_pp" => do_dat_pp,;

                                                   "%grupa_var" => grupa_var} )

//place timer here

nSec := Seconds()

// Why the question mark //?oRs:=oC:Execute(cSql)

oRs:=oC:Execute(cSql)

? Seconds()-nSec //This is your response time

Getting back to the difference in response time:

1. the provider used to connect can make a difference, currently the last provider should be MSOLEDBSQL but this should not make so much difference and my experience using it gives me errors when retrieving images.

2, How are you timing the response time?

Regards,

David Field

[David Field]

The text between TEXT INTO and ENDTEXT should have read exactly like your SQLQuery.sql whit only the difference in the DECLARE statements.

Google group changed the entire thing when published.

I hope you get the idea.

Regards,

David Field

[Andreja Starcevic]

Dear David,

Thanks for suggestions.

The idea for code to be more readable is very nice because I always try sql code in studio before implementing to my app.

-oc:CommandTimeOut := 5 // or 10 -- if I put this instead of 0 I get error "query time out expired"

I have placed timer and I get values of around 170

Tried changing provider but the value is the same.

Kind regards

[David Field]

170 seconds is very high and the difference between ms studio and ADO should not be more that 1 or 2 seconds.

Did you remove the question mark from ?oRs := oC:Execute(cSql) ?

Did you try memowrit("query.sql", cSql) and use result in ms studio?

Maybe there is a small difference between your prg query and the one you use in ms studio?

[Andreja Starcevic]

Dear David,

You are correct!

You advice gave me a path to solution.

When I did memowrit and tested it it also took 170 second so in fact its an error in code.

In my code I have one more unnecessary JOIN which slows me down.

After removing it query time is 4 seconds- the same as in SQL studio.

Thank you very much