CODEWITHSUNDEEP
performancesql-server-2012
Wimdp
Wimdp

Reputation: 49

Optimising a SQL query which is taking a long time to execute

I have a simple database on which I want to do a simple query.

These are the columns for my database table:

The columns are:

The query I want to execute got as goal to get the highest and lowest value of the sum of the value of each day.

As you see, each day got divided in hours, have to check or the datum is the same or changes and get the total value by that.

This is my query: 

Declare @totaal bigInt
Declare @tussentotaal bigint
Declare @Datum varchar
Declare @datumverschil varchar
Declare @hoogste bigint
Declare @laagste bigint
Declare @teller bigint
Declare @tellettotaal bigint

set @tellettotaal = (select count(*) from cresent_opdracht_de_proost_wim.dbo.[test])

Set @teller = 1

SET @datum = (Select top(1) datum 
              from cresent_opdracht_de_proost_wim.dbo.[test] 
              order by afronden asc)

Set @datumverschil = @Datum
set @tussentotaal = 0
set @totaal = 0
set @hoogste = 1775000006856
set @laagste = 1775000006856

while @teller <= @tellettotaal
begin
  if @teller = 1
  Begin
    set @tussentotaal = (select top(1) value 
                         from cresent_opdracht_de_proost_wim.dbo.[test] 
                         order by afronden asc)

    if @tussentotaal != 0
    begin
      Set @tussentotaal = @tussentotaal/100
    end
  End
  Else
  begin
    SET @tussentotaal = (Select top(1) value 
                         from (select top (@teller) * 
                               from cresent_opdracht_de_proost_wim.dbo.[test]) q 
                         order by afronden desc)

    Set @tussentotaal = @tussentotaal/100
  end

  if @tussentotaal != 0 
  Begin
    Set @totaal = @totaal + @tussentotaal
  end

  SET @teller= @teller + 1

  Set @datumverschil = (Select top(1) datum 
                        from (select top (@teller) * 
                              from cresent_opdracht_de_proost_wim.dbo.[test]) q 
                        order by afronden desc)

  if @datum != @datumverschil
  Begin
    if @totaal >= @hoogste
    begin
      set @hoogste = @totaal
    end

    if @totaal <= @laagste
    begin
      if @totaal != 0
      Begin
        set @laagste = @totaal
      end
    end

    Set @datum = @datumverschil
    set @totaal = 0
    select @teller As teller
  end
end

Select @hoogste As hoogste
Select @laagste As laagste

After after 22 minutes only 44000 rows were processed.

Does anybody know how I can optimise my query?

Upvotes: 2

Views: 1027

Answers (1)

TomTom
TomTom

Reputation: 62157

Hm, ever thought that it may be an idea not to use a broken procedural approach?

First, your logic may be broken - the time osounds too large, so maybe you just have a dead loop. Sorry, it is very hard for most peope to understand fringe langauges - i.e. anything that is not english, so you table and field names make no sense. Nonsense hardly is debuggable by people not understanting it.

THAT SAID: I think you can do with a LOT less queries in total by not running a senseless loop over top 1 - instead get everything ordered descending. Brutallly speaking: Get rid of the loop and make ONE Statement that compiles your result set. Seriously. That should be possible. Do not try to outsmart a query optimizer and go back to write procedural database code like many people did 20 years ago at dbase times. In general. your query is linear which means no parallelization and your micro managing of conditions means no query optimizer can be doind smart things (as optimization is statement by statement). Define the result set in one statement and you may wonder how efficient is it.

I am quite sure at the end you will find out that you basically try to outsmart something that is better than you writing queries. Use SQL - define the result set.

And finally, without knowing your data distribution and - ouch - indices - we can not really help. Maybe you simply miss any sensible index? Who knows - you dont tell us anything (no query plan output, no table defintiions, no index definitions).

It would possibly also have not to abuse data types: Declare @Datum varchar - OUCH. Ever heard of the DATE data type in sql server?

Upvotes: 1

Related Questions