Is this SQL Query inefficient?

  • 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

  • 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

     

  • 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

  • It could be that

    pressure_date.datetime AS pressure_date

    should be

    pressure_data.datetime AS pressure_date

     

    Mattie

     

     

  • 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

  • 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