November 21, 2005 at 2:33 am
Dear all
I was hoping you could give me some advice, I am totally new to SQL and SQL server.
I have doubts about the quality of the SQL scripts that are used in our database system, the replies from the database seem to be abnormally slow and we have a very powerful machine.
We have run a tool called SQL profiler that allows one to see what queries are run on the database, and further down i show one of the ones we see.
My question is, if you've got time for it, what sort of extra information about the database would one need to know if this is a good query or not? Or is it possible to tell straight away? Would you be able to suggest a more efficient alternative?
SELECT id_weather_data,
(SELECT datetime FROM weather_data WHERE id = a.id_weather_data) AS weather_date,id_pressure_data, (SELECT datetime FROM pressure_data WHERE id = a.id_pressure_data) AS pressure_date,id_hidro_data, (SELECT datetime FROM hidro_data WHERE id = a.id_hidro_data) AS hidro_date,id_wind_data, (SELECT datetime FROM wind_data WHERE id = a.id_wind_data) AS wind_date,id_raas_data, (SELECT datetime FROM raas_data WHERE id = a.id_raas_data) AS raas_date FROM LAST_DATA a where a.id_teams=28
Thanks for your help
Eduardo
November 21, 2005 at 3:10 pm
1) the query has extra subselects in the select clause, move them where possible in the from or where clause.
Have a look at
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
and
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
to analyze query plans.
a try to reconstruct the query (non tested)
SELECT
id_weather_data
,weather_data.datetime as weather_date
,id_pressure_data
,pressure_date.datetime AS pressure_date
,id_hidro_data
,hidro_data.datetime as hidro_date
,id_wind_data
,wind_data.datetime as id_wind_data
,id_raas_data
,raas_data.datetime as raas_date
FROM LAST_DATA a
inner join weather_data weather_data
on a.id_weather_data=weather_data.id
inner join pressure_data pressure_data
ON pressure_data.id = a.id_pressure_data
inner join hidro_data hidro_data
on hidro_data.id = a.id_hidro_data
inner join wind_data
on wind_data.id= a.id_wind_data
inner join raas_data
on raas_data.id= a.id_raas_data
where a.id_teams=28
November 22, 2005 at 6:09 am
Thank you very much for taking the time to answer and even reconstructing the query!
I have tried it and i get
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'pressure_date' does not match with a table name or alias name used in the query.
Is it something obvious I'm doing wrong?
I will also try to read & understand the links you've sent
Cheers
November 22, 2005 at 7:08 am
It could be that
pressure_date.datetime AS pressure_date
should be
pressure_data.datetime AS pressure_date
Mattie
November 22, 2005 at 9:40 am
Be sure to keep in mind that in the original query, the nested subselects would not cause rows from LAST_DATA to display so you would get all relevant matches.
In the modified format, the use of inner joins on each of the supporting tables can result in less data rows being returned because you have used inner join for each of those tables. So you may want to consider changing them each to left outer joins to insure that you get the same number of rows in the query that you did before.
SELECT
id_weather_data
,weather_data.datetime as weather_date
,id_pressure_data
,pressure_data.datetime AS pressure_date
,id_hidro_data
,hidro_data.datetime as hidro_date
,id_wind_data
,wind_data.datetime as id_wind_data
,id_raas_data
,raas_data.datetime as raas_date
FROM LAST_DATA a
left outer join weather_data on a.id_weather_data=weather_data.id
left outer join pressure_data ON pressure_data.id = a.id_pressure_data
left outer join hidro_data on hidro_data.id = a.id_hidro_data
left outer join wind_data on wind_data.id= a.id_wind_data
left outer join raas_data on raas_data.id= a.id_raas_data
where a.id_teams=28
December 1, 2005 at 4:44 am
Thank you all for your time and suggestions, it is much appreciated.
I am now suspecting that this query is not the main culprit, i have posted a thread with the new suspicions, because the query that causes the trouble is another one and it is all a bit strange.
The new thread is in Database/General and is called "Query is slower when requested by a external program"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply