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
 General SQL Server Forums
 New to SQL Server Programming
 What is wrong (MySQL vs MsSQL)

Author  Topic 

Bokazoit
Starting Member

6 Posts
Posted - 2008-01-28 : 10:08:21
I have been using MySQL for a long time but because of company policy changes I have started to switch from MySQL to MsSQL

OMG MsSQL is very slow with every thing it does compared to MySQL. I.e updating 300.000+) data in a table with 15 million+ records using 'update' syntax. In MySQL it was done in 4 minutes. In MsSQL it was not done in 20 minutes (and then I stoped it in anger)

I have a script that I have created to give our daily revenue. In MySQL that takes 30 sec to finish. In MsSQL it takes atleast 1.30 minutes.

I have 1,5G of memory and MsSQL uses it all. MySQL uses 200mb at most.

What am I doing wrong. Is it the memory or what can it be? I have indexed the tables in the same way. I really don't know what to do anymore.

Perhaps You can give me some hints

Tx in advance ;-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-01-28 : 10:12:53
Perhaps you could show us the scripts?

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2008-01-28 : 10:12:58
you haven't given us any information we can use to help you.

please post your table ddl, some sample data and the queries you want to improve.



-ec
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2008-01-28 : 19:05:45
You need to consider such things as indexing strategy to maximise performance on various queries

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

Bokazoit
Starting Member

6 Posts
Posted - 2008-01-29 : 02:07:44
Here is the MySQL script:

  set @from_date = '2008-01-01';
  set @to_date = last_day(@from_date);


###Tabellen 'Allekunder' dannes
###Indeholder info om hver enkelt kundes aktiviteter i den givne periode

DROP TABLE IF EXISTS reports.daily;

  CREATE table reports.daily (
    dato date,
    day_of_date varchar(20) default '-',
    ugedag int null,
    sales double null,
    mersalg double null,
    callc int null,
    warehouse int null,
    cost double null,
    DB double null,
    DG double null,
    orderlines double null,
    orders double null,
    salesinvoice double null,
    linesinvoice double null,
    websales double null,
    webpct double null
);

CALL quilldb.datecreate(@from_date, @to_date);

update reports.daily set ugedag = weekofyear(dato) where dato <> '';

update reports.daily a,
(select a.fakturadato, a.sales+b.gebyr as sales, a.cost, (a.sales+b.gebyr-a.cost) as DB, (a.sales+b.gebyr-a.cost)/a.sales as DG, a.orderlines, a.orders, (a.sales+b.gebyr)/a.orders as omsfak, a.orderlines/a.orders as linfak from
(select c.fakturadato, sum(c.liniebeløb*b.kurs) as sales, count(distinct c.fakturanummer) as orders, count(c.fakturanummer) as orderlines, sum(c.kostværdi*b.kurs) as cost,
  count(distinct b.bestilkonto) as actives from quilldb.debkart a, quilldb.debfakjour b, quilldb.debfakpost c
  where a.konto = b.bestilkonto and b.fakturanummer = c.fakturanummer and
  c.fakturadato between @from_date and @to_date
  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135') group by c.fakturadato) a,
(select b.fakturadato, sum(b.gebyr*b.kurs) as gebyr from quilldb.debkart a, quilldb.debfakjour b, (select c.fakturanummer
  from quilldb.debfakpost c where c.fakturadato between @from_date and @to_date
  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
  group by c.fakturanummer) c where a.konto = b.bestilkonto and b.fakturanummer = c.fakturanummer group by b.fakturadato) b
where a.fakturadato = b.fakturadato
) b
set a.sales = b.sales, a.cost = b.cost, a.db = b.db, a.dg = b.dg, a.orderlines = b.orderlines, a.orders = b.orders, a.salesinvoice = b.omsfak, a.linesinvoice = b.linfak where a.dato = b.fakturadato;

update reports.daily a,
(select c.fakturadato, c.websales+d.webgebyr as websales from
(select c.fakturadato, sum(c.liniebeløb*b.kurs) as websales from quilldb.debkart a, quilldb.debfakjour b, quilldb.debfakpost c
  where a.konto = b.bestilkonto and b.webordre = 'ja' and b.fakturanummer = c.fakturanummer and
  c.fakturadato between @from_date and @to_date
  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135') group by c.fakturadato) c,
(select b.fakturadato, sum(b.gebyr*b.kurs) as webgebyr from quilldb.debkart a, quilldb.debfakjour b, (select c.fakturanummer
  from quilldb.debfakpost c where c.fakturadato between @from_date and @to_date
  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
  group by c.fakturanummer) c where a.konto = b.bestilkonto and b.webordre = 'ja' and b.fakturanummer = c.fakturanummer group by b.fakturadato) d
where c.fakturadato = d.fakturadato
) b
set a.websales = b.websales where a.dato = b.fakturadato;

update reports.daily a set a.webpct = a.websales/a.sales;

update reports.daily a, (select ugedag, sum(websales)/sum(sales) as webpct from reports.daily group by ugedag) b set a.webpct = b.webpct where a.ugedag = b.ugedag and day_of_date = 'saturday';

call quilldb.orderedsale(@from_date, @to_date);
update reports.daily set dg = (sales - cost)/sales, salesinvoice = sales/orders, linesinvoice = orderlines/orders, webpct = websales/sales where day_of_date = 'monday';
update reports.daily set dg = (sales - cost)/sales, salesinvoice = sales/orders, linesinvoice = orderlines/orders, webpct = websales/sales where day_of_date = 'friday';
update reports.daily set day_of_date = 'Mersalg' where day_of_date = 'Saturday';
update reports.daily set day_of_date = '' where day_of_date = 'sunday';
update reports.daily set dato = null, day_of_date = 'Mersalg' where dato > last_day(@from_date);

insert into reports.daily values(null, 'Last Year', 0, 0, null, null, null, 0, null, null, null, null, null, null, null, null);
insert into reports.daily values(null, 'Max måned', 0, 0, null, null, null, 0, null, null, null, null, null, null, null, null);

SELECT @minuge := min(ugedag) FROM reports.daily d;
SELECT @maxuge := max(ugedag) FROM reports.daily d;

Call quilldb.mersalg(@minuge, @maxuge);

update reports.daily a,
(select sum(c.liniebeløb*b.kurs) +
  ((select sum(b.gebyr*b.kurs) from quilldb.debkart a, quilldb.debfakjour b, (select c.fakturanummer
    from quilldb.debfakpost c where c.fakturadato between date_sub(@from_date, interval 1 year) and date_sub(@to_date, interval 1 year)
    and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
    group by c.fakturanummer) c where a.konto = b.bestilkonto and b.fakturanummer = c.fakturanummer)) as sales,
  sum(c.kostværdi*b.kurs) as cost from quilldb.debkart a, quilldb.debfakjour b, quilldb.debfakpost c
where a.konto = b.bestilkonto and b.fakturanummer = c.fakturanummer and
c.fakturadato between date_sub(@from_date, interval 1 year) and date_sub(@to_date, interval 1 year)
and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
) b
set a.ugedag = year(@from_date)-1, a.sales = b.sales, a.cost = b.cost where day_of_date = 'Last Year';

  set @mthmin = (SELECT quilldb.bestmth(@from_date, @to_date, month(@from_date)));
  set @mthmax = last_day(@mthmin);

update reports.daily a,
(select sum(c.liniebeløb*b.kurs) +
  ((select sum(b.gebyr*b.kurs) from quilldb.debkart a, quilldb.debfakjour b, (select c.fakturanummer
    from quilldb.debfakpost c where c.fakturadato between @mthmin and @mthmax
    and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
    group by c.fakturanummer) c where a.konto = b.bestilkonto and b.fakturanummer = c.fakturanummer)) as sales,
  sum(c.kostværdi*b.kurs) as cost from quilldb.debkart a, quilldb.debfakjour b, quilldb.debfakpost c
where a.konto = b.bestilkonto and b.fakturanummer = c.fakturanummer and
c.fakturadato between @mthmin and @mthmax
and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
) b
set a.ugedag = month(@mthmin), a.sales = b.sales, a.cost = b.cost where day_of_date = 'Max måned';

alter table reports.daily modify column dato varchar(20) default null;

update reports.daily set dato = DATE_FORMAT(dato,'%d.%M') where dato <> '';


and here is the MsSQL script:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Skov Hansen
-- Create date: 2008-01-09
-- Description:	procedure til dagsrapport
-- =============================================
ALTER PROCEDURE [reports].[dailyproc] 
	-- Add the parameters for the stored procedure here
	@from_date smalldatetime
AS
BEGIN

	declare @to_date smalldatetime
	declare @minuge int
	declare @maxuge int
	declare @mthmin smalldatetime
	declare @mthmax smalldatetime	
    declare @x int
    declare @y smalldatetime
	declare @uge int
	declare @mindate smalldatetime
	declare @maxdate smalldatetime

	set @to_date = dateadd(day, -1, dateadd(month, 1, @from_date))
    set @x = 0
    set @y = @from_date

	DROP TABLE reports.daily;

	CREATE table reports.daily (
		dato smalldatetime,
		day_of_date varchar(20),
		ugedag int null,
		sales decimal(12,4) null,
		mersalg decimal(12,4) null,
		callc int null,
		warehouse int null,
		cost decimal(12,4) null,
		DB decimal(12,4) null,
		DG decimal(12,4) null,
		orderlines decimal(12,4) null,
		orders decimal(12,4) null,
		salesinvoice decimal(12,4) null,
		linesinvoice decimal(12,4) null,
		websales decimal(12,4) null,
		webpct decimal(12,4) null
)

    while @y <= @to_date
    	begin
    		set @y = DATEADD(day, @x, @from_date)
    		insert into reports.daily (dato, day_of_date)
    			select @y, datename(weekday, @y)
			set @x = @x + 1
    	end

	update reports.daily set ugedag = datename(week, dato) where dato <> ''

--	update reports.daily set sales = b.sales, cost = b.cost, db = b.db, dg = (b.sales-b.cost)/b.sales, orderlines = b.orderlines, orders = b.orders, salesinvoice = b.sales/b.orders, linesinvoice = cast(b.orderlines as float)/cast(b.orders as float), webpct = b.webpct, websales = b.websales from
--	(select a.fakturadato, a.sales+b.gebyr as sales, a.cost, (a.sales+b.gebyr-a.cost) as DB, a.orderlines, a.orders, (c.websales+d.webgebyr)/(a.sales+b.gebyr) as webpct, c.websales+d.webgebyr as websales from
--	(select c.fakturadato, sum(c.liniebeløb*b.kurs) as sales, count(distinct c.fakturanummer) as orders, count(c.fakturanummer) as orderlines, sum(c.kostværdi*b.kurs) as cost,
--	  count(distinct b.bestilkonto) as actives from quilldb.debfakjour b, quilldb.debfakpost c
--	  where b.fakturanummer = c.fakturanummer and c.fakturadato between @from_date and @to_date
--	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135') group by c.fakturadato) a,
--	(select b.fakturadato, sum(b.gebyr*b.kurs) as gebyr from quilldb.debfakjour b, (select c.fakturanummer
--	  from quilldb.debfakpost c where c.fakturadato between @from_date and @to_date
--	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
--	  group by c.fakturanummer) c where b.fakturanummer = c.fakturanummer group by b.fakturadato) b,
--	(select c.fakturadato, sum(c.liniebeløb*b.kurs) as websales from quilldb.debfakjour b, quilldb.debfakpost c
--	  where b.webordre = 'Ja' and b.fakturanummer = c.fakturanummer and c.fakturadato between @from_date and @to_date
--	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135') group by c.fakturadato) c,
--	(select b.fakturadato, sum(b.gebyr*b.kurs) as webgebyr from quilldb.debfakjour b, (select c.fakturanummer
--	  from quilldb.debfakpost c where c.fakturadato between @from_date and @to_date
--	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
--	  group by c.fakturanummer) c where b.webordre = 'Ja' and b.fakturanummer = c.fakturanummer group by b.fakturadato) d
--	where a.fakturadato = b.fakturadato and b.fakturadato = c.fakturadato and c.fakturadato = d.fakturadato
--	) b where dato = b.fakturadato

	update reports.daily set sales = b.sales, cost = b.cost, db = b.db, dg = (b.sales-b.cost)/b.sales, orderlines = b.orderlines, orders = b.orders, salesinvoice = b.sales/b.orders, linesinvoice = cast(b.orderlines as float)/cast(b.orders as float) from
	(select a.fakturadato, a.sales+b.gebyr as sales, a.cost, (a.sales+b.gebyr-a.cost) as DB, a.orderlines, a.orders from
	(select c.fakturadato, sum(c.liniebeløb*b.kurs) as sales, count(distinct c.fakturanummer) as orders, count(c.fakturanummer) as orderlines, sum(c.kostværdi*b.kurs) as cost,
	  count(distinct b.bestilkonto) as actives from quilldb.debfakjour b, quilldb.debfakpost c
	  where b.fakturanummer = c.fakturanummer and c.fakturadato between @from_date and @to_date
	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135') group by c.fakturadato) a,
	(select b.fakturadato, sum(b.gebyr*b.kurs) as gebyr from quilldb.debfakjour b, (select c.fakturanummer
	  from quilldb.debfakpost c where c.fakturadato between @from_date and @to_date
	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
	  group by c.fakturanummer) c where b.fakturanummer = c.fakturanummer group by b.fakturadato) b
	where a.fakturadato = b.fakturadato
	) b where dato = b.fakturadato

	update reports.daily set websales = b.websales from
	(select c.fakturadato, c.websales+d.webgebyr as websales from
	(select c.fakturadato, sum(c.liniebeløb*b.kurs) as websales from quilldb.debfakjour b, quilldb.debfakpost c
	  where b.webordre = 'Ja' and b.fakturanummer = c.fakturanummer and c.fakturadato between @from_date and @to_date
	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135') group by c.fakturadato) c,
	(select b.fakturadato, sum(b.gebyr*b.kurs) as webgebyr from quilldb.debfakjour b, (select c.fakturanummer
	  from quilldb.debfakpost c where c.fakturadato between @from_date and @to_date
	  and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
	  group by c.fakturanummer) c where b.webordre = 'Ja' and b.fakturanummer = c.fakturanummer group by b.fakturadato) d
	where c.fakturadato = d.fakturadato
	) b where dato = b.fakturadato

	update reports.daily set webpct = websales/sales

	update reports.daily set webpct = b.webpct from (select ugedag as newugedag, sum(websales)/sum(sales) as webpct from reports.daily group by ugedag) b where ugedag = b.newugedag and day_of_date = 'saturday'

    set @x = 0
    set @y = @from_date
    while @y < @to_date
    	begin
			if ((day(@y) = 1 or day(@y) = 2) and (datename(weekday,@y) = 'Saturday' or datename(weekday,@y) = 'Sunday'))
				begin
					update reports.daily set sales = sales + b.salesb, cost = cost + b.costb, db = db + b.dbb, orderlines = orderlines + b.orderlinesb, orders = orders + b.ordersb, websales = websales + b.websalesb from (select isnull(ugedag,0) as ugedagb, isnull(sales,0) as salesb, isnull(cost,0) as costb, isnull(db,0) as dbb, isnull(orderlines,0) as orderlinesb, isnull(orders,0) as ordersb, isnull(websales,0) as websalesb from reports.daily where dato = @y) b where ugedag = datename(week,@y) + 1 and day_of_date = 'Monday'
					update reports.daily set dato = null, sales = null, cost = null, db = null, dg = null, orderlines = null, orders = null, websales = null, salesinvoice = null, linesinvoice = null where ugedag = datename(week,@y) and day_of_date = datename(weekday,@y) 
				end
			else
				if (datename(weekday,@y) = 'Saturday' or datename(weekday,@y) = 'Sunday')
					begin
						update reports.daily set sales = sales + b.salesb, cost = cost + b.costb, db = db + b.dbb, orderlines = orderlines + b.orderlinesb, orders = orders + b.ordersb, websales = websales + b.websalesb from (select isnull(ugedag,0) as ugedagb, isnull(sales,0) as salesb, isnull(cost,0) as costb, isnull(db,0) as dbb, isnull(orderlines,0) as orderlinesb, isnull(orders,0) as ordersb, isnull(websales,0) as websalesb from reports.daily where dato = @y) b where ugedag = datename(week,@y) and day_of_date = 'Friday'
						update reports.daily set dato = null, sales = null, cost = null, db = null, dg = null, orderlines = null, orders = null, websales = null, salesinvoice = null, linesinvoice = null where ugedag = datename(week,@y) and day_of_date = datename(weekday,@y)
					end
			set @x = @x + 1
			set @y = dateadd(day, @x, @from_date)
    	end

	update reports.daily set dg = (sales - cost)/sales, salesinvoice = sales/orders, linesinvoice = orderlines/orders, webpct = websales/sales where day_of_date = 'monday'
	update reports.daily set dg = (sales - cost)/sales, salesinvoice = sales/orders, linesinvoice = orderlines/orders, webpct = websales/sales where day_of_date = 'friday'

	update reports.daily set day_of_date = 'Mersalg' where day_of_date = 'Saturday'
	update reports.daily set day_of_date = '' where day_of_date = 'sunday'
	update reports.daily set dato = null, day_of_date = 'Mersalg' where month(dato) > month(@from_date)

	insert into reports.daily values(null, 'Last Year', 0, 0, null, null, null, 0, null, null, null, null, null, null, null, null)
	insert into reports.daily values(null, 'Max måned', 0, 0, null, null, null, 0, null, null, null, null, null, null, null, null)

	select @minuge = min(ugedag) from reports.daily
	select @maxuge = max(ugedag) from reports.daily

    set @uge = @minuge
    while @uge < @maxuge + 1
    	begin
			select @mindate = min(dato) from reports.daily d where d.ugedag = @uge
			select @maxdate = max(dato) from reports.daily d where d.ugedag = @uge
			update reports.daily set mersalg = b.xtrasalg from
				(select a.opsalg + b.xpsalg as xtrasalg from
				(select sum(liniebeløb)*0.4 as opsalg from quilldb.debfakpost where fakturadato between @mindate and @maxdate and mersalg = 'Opsalg') a,
				(select sum(liniebeløb) as xpsalg from quilldb.debfakpost where fakturadato between @mindate and @maxdate and (mersalg = 'Plussalg' or mersalg = 'Krydssalg')) b) b
			where day_of_date = 'mersalg' and ugedag = @uge 
			set @uge = @uge + 1
    	end

	update reports.daily set ugedag = year(@from_date)-1, sales = b.sales, cost = b.cost from
	(select sum(c.liniebeløb*b.kurs) +
	  ((select sum(b.gebyr*b.kurs) from quilldb.debfakjour b, (select c.fakturanummer
		from quilldb.debfakpost c where c.fakturadato between dateadd(year, -1, @from_date) and dateadd(year, -1, @to_date)
		and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
		group by c.fakturanummer) c where b.fakturanummer = c.fakturanummer)) as sales,
	  sum(c.kostværdi*b.kurs) as cost from quilldb.debfakjour b, quilldb.debfakpost c
	where b.fakturanummer = c.fakturanummer and
	c.fakturadato between dateadd(year, -1, @from_date) and dateadd(year, -1, @to_date)
	and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')) b
	where day_of_date = 'Last Year' 

		select @mthmin = reports.bestmth(@from_date, @to_date, month(@from_date))
		select @mthmax = dateadd(day, -1, dateadd(month, 1, @mthmin))

	update reports.daily set ugedag = month(@mthmin), sales = b.sales, cost = b.cost from
	(select sum(c.liniebeløb*b.kurs) +
	  ((select sum(b.gebyr*b.kurs) from quilldb.debfakjour b, (select c.fakturanummer
		from quilldb.debfakpost c where c.fakturadato between @mthmin and @mthmax
		and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
		group by c.fakturanummer) c where b.fakturanummer = c.fakturanummer)) as sales,
	  sum(c.kostværdi*b.kurs) as cost from quilldb.debfakjour b, quilldb.debfakpost c
	where b.fakturanummer = c.fakturanummer and
	c.fakturadato between @mthmin and @mthmax
	and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')) b
	where day_of_date = 'Max måned'

	alter table reports.daily alter column dato varchar(20) null

	update reports.daily set dato = cast(day(dato) as nvarchar(4)) + '. ' + cast(datename(month, dato) as nvarchar(12)) where dato <> ''

	select isnull(dato, ''), isnull(day_of_date, ''), isnull(ugedag, ''), isnull(replace(sales, '.', ','), '') as sales, isnull(replace(mersalg, '.', ','), '') as mersalg, isnull(callc, '') as callc, isnull(warehouse, '') as warehouse, isnull(replace(cost, '.', ','), '') as cost, isnull(replace(db, '.', ','), '') as db, isnull(replace(dg, '.', ','), '') as dg, isnull(replace(orderlines, '.', ','), '') as orderlines, isnull(replace(orders, '.', ','), '') as orders, isnull(replace(salesinvoice, '.', ','), '') as salesinvoice, isnull(replace(linesinvoice, '.', ','), '') as linesinvoice, isnull(replace(websales, '.', ','), '') as websales, isnull(replace(webpct, '.', ','), '') as webpct from reports.daily

END


The MsSQL is a procedure but the code is the same. I just made the procedures in the MySQL script and calls them.

@jackv: I use indexing on all the variables that coonects to each other on every table

@eyechart: table ddl? how to retrieve that?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-01-29 : 02:16:10
You should try to rewrite all correlated subqueries and use JOINs instead.
Also, several of the UPDATEs can be merged into one single UPDATE.

The MySQL query engine may be more intelligent with correlated subqueries than Microsoft.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Bokazoit
Starting Member

6 Posts
Posted - 2008-01-29 : 02:29:08
Is joins better? and which update queries can be merged into one? :)
Go to Top of Page

Bokazoit
Starting Member

6 Posts
Posted - 2008-01-29 : 02:51:15
And wouldn't an update of 300k+ records be better than to delete the same records and then insert again? Update procedure in MsSQL was a killer and after 20 min. I stoped it while the same procedure in MySQL took no longer than 4 min. That's strange :p
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-01-29 : 03:51:42
Here is a starter.
What I can see, you could replace all this UPDATE with a single PIVOT query to get what yuou want.
And if you are using SQL Server 2005, thing are more easy.
The trick is to minimize the number of reads, of which you have plenty!

When using these two tables {quilldb.debfakjour, quilldb.debfakpost} you should only use them once and get all data back directly. Not cusing them over and over again for same WHERE filter...
ALTER PROCEDURE [reports].[dailyproc] 
(
	@from_date SMALLDATETIME
)
AS

-- Prevent unwanted resultsets back to client
SET NOCOUNT ON

-- Initialize variables
DECLARE	@to_date smalldatetime,
	@minuge int,
	@maxuge int,
	@mthmin smalldatetime,
	@mthmax smalldatetime	,
	@uge int,
	@mindate smalldatetime,
	@maxdate smalldatetime

-- Prepare user supplied parameter
SELECT	@from_date = DATEADD(DAY, DATEDIFF(DAY, '19000101', @from_date), '19000101'),
	@to_date = DATEADD(MONTH, 1, @from_date)

-- Remove all previous records
TRUNCATE TABLE	reports.daily;

-- Insert nevessary dates
INSERT		reports.daily
		(
			dato
		)
SELECT		DATEADD(DAY, Number, @from_date)
FROM		master..spt_values
WHERE		Type = 'p'
		AND Number < DATEDIFF(DAY, @from_date, @todate)
ORDER BY	Number

-- Update weekday and week columns
UPDATE	reports.daily
SET	day_of_date = DATENAME(WEEKDAY, dato),
	ugedag = DATENAME(WEEK, dato)

-- Update some columns
UPDATE		y
SET		y.sales = q.sales,
		y.cost = q.cost,
		y.db = q.db,
		y.dg = 1.0 * (q.sales - q.cost) / q.sales,
		y.orderlines = q.orderlines,
		y.orders = q.orders,
		y.salesinvoice = 1.0 * q.sales / q.orders,
		y.linesinvoice = 1.0 * q.orderlines / q.orders,
		y.websales = q.websales
FROM		reports.daily AS y
INNER JOIN	(
			SELECT		a.fakturadato,
					a.sales + b.gebyr AS sales,
					a.cost,
					a.sales + b.gebyr - a.cost AS DB,
					a.orderlines,
					a.orders,
					a.websales + b.webgebyr AS websales
			FROM		(
						SELECT		c.fakturadato,
								SUM(c.liniebeløb * b.kurs) AS sales,
								COUNT(DISTINCT c.fakturanummer) AS orders,
								COUNT(c.fakturanummer) AS orderlines,
								SUM(c.kostværdi * b.kurs) AS cost,
								COUNT(DISTINCT b.bestilkonto) AS actives,
								SUM(CASE WHEN b.webordre = 'Ja' THEN c.liniebeløb * b.kurs ELSE 0 END) as websales
						FROM		quilldb.debfakjour AS b
						INNER JOIN	quilldb.debfakpost AS c ON c.fakturanummer = b.fakturanummer
						WHERE		c.fakturadato >= @from_date
								AND c.fakturadato < @to_date
								AND (
									c.varenummer <= '2021220'
									OR c.varenummer BETWEEN '2100102' AND '8809999'
									OR c.varenummer BETWEEN '9900130' AND '9900140'
									OR c.varenummer >= '9999135'
								)
						GROUP BY	c.fakturadato
					) AS a
			INNER JOIN	(
						SELECT		b.fakturadato,
								SUM(b.gebyr * b.kurs) as gebyr,
								SUM(CASE WHEN b.webordre = 'Ja' THEN b.gebyr * b.kurs ELSE 0 END) AS webgebyr
						FROM		quilldb.debfakjour AS b
						INNER JOIN	(
									SELECT		fakturanummer
									FROM		quilldb.debfakpost
									WHERE		fakturadato >= @from_date
											AND fakturadato < @to_date
											AND (
												c.varenummer <= '2021220'
												OR c.varenummer between '2100102' AND '8809999'
												OR c.varenummer BETWEEN '9900130' AND '9900140'
												OR c.varenummer >= '9999135'
											)
									GROUP BY	fakturanummer
								) AS c ON c.fakturanummer = b.fakturanummer
						GROUP BY	b.fakturadato
					) AS b ON b.fakturadato = a.fakturadato
		) AS q ON q.fakturadato = y.dato

-- Update some percentage columns
UPDATE	reports.daily
SET	webpct = 1.0 * websales / sales

UPDATE		y
SET		y.webpct = b.webpct
FROM		reports.daily AS y
INNER JOIN	(
			SELECT		ugedag AS newugedag,
					1.0 * sum(websales) / sum(sales) AS webpct
			FROM		reports.daily
			GROUP BY	ugedag
		) AS b ON b.newugedag = y.ugedag
WHERE		y.day_of_date = 'saturday'

-- Do a running total
    set @x = 0
    set @y = @from_date
    while @y < @to_date
    	begin
		IF DAY(@y) IN (1, 2) AND DATENAME(WEEKDAY, @y) IN ('Saturday', 'Sunday')
		BEGIN
			UPDATE	reports.daily
			set	sales = sales + b.salesb,
				cost = cost + b.costb,
				db = db + b.dbb,
				orderlines = orderlines + b.orderlinesb,
				orders = orders + b.ordersb,
				websales = websales + b.websalesb
			from	(
					select	isnull(ugedag,0) as ugedagb,
						isnull(sales,0) as salesb,
						isnull(cost,0) as costb,
						isnull(db,0) as dbb,
						isnull(orderlines,0) as orderlinesb,
						isnull(orders,0) as ordersb,
						isnull(websales,0) as websalesb
					from	reports.daily
					where	dato = @y
				) AS b
			where	ugedag = datename(week, @y) + 1
				and day_of_date = 'Monday'

				update reports.daily set dato = null, sales = null, cost = null, db = null, dg = null, orderlines = null, orders = null, websales = null, salesinvoice = null, linesinvoice = null where ugedag = datename(week,@y) and day_of_date = datename(weekday,@y) 
			end
		else
			if (datename(weekday,@y) = 'Saturday' or datename(weekday,@y) = 'Sunday')
				begin
					update reports.daily set sales = sales + b.salesb, cost = cost + b.costb, db = db + b.dbb, orderlines = orderlines + b.orderlinesb, orders = orders + b.ordersb, websales = websales + b.websalesb from (select isnull(ugedag,0) as ugedagb, isnull(sales,0) as salesb, isnull(cost,0) as costb, isnull(db,0) as dbb, isnull(orderlines,0) as orderlinesb, isnull(orders,0) as ordersb, isnull(websales,0) as websalesb from reports.daily where dato = @y) b where ugedag = datename(week,@y) and day_of_date = 'Friday'
					update reports.daily set dato = null, sales = null, cost = null, db = null, dg = null, orderlines = null, orders = null, websales = null, salesinvoice = null, linesinvoice = null where ugedag = datename(week,@y) and day_of_date = datename(weekday,@y)
				end
		set @x = @x + 1
		set @y = dateadd(day, @x, @from_date)
    	end

update	reports.daily
set	dg = 1.0 * (sales - cost) / sales,
	salesinvoice = 1.0 * sales / orders,
	linesinvoice = 1.0 * orderlines / orders,
	webpct = 1.0 * websales / sales
where	day_of_date IN ('monday', 'friday')

update	reports.daily
set	day_of_date = 'Mersalg'
where	day_of_date = 'Saturday'

update	reports.daily
set	day_of_date = ''
where	day_of_date = 'sunday'

update	reports.daily
set	dato = null,
	day_of_date = 'Mersalg'
where	DATEDIFF(MONTH, '19000101', dato) > DATEDIFF(MONTH, '19000101', @from_date)

INSERT	reports.daily
SELECT	null,
	'Last Year',
	0,
	0,
	null,
	null,
	null,
	0,
	null,
	null,
	null,
	null,
	null,
	null,
	null,
	null

UNION ALL

SELECT	null,
	'Max måned',
	0,
	0,
	null,
	null,
	null,
	0,
	null,
	null,
	null,
	null,
	null,
	null,
	null,
	null

	select @minuge = min(ugedag) from reports.daily
	select @maxuge = max(ugedag) from reports.daily

    set @uge = @minuge
    while @uge < @maxuge + 1
    	begin
			select @mindate = min(dato) from reports.daily d where d.ugedag = @uge
			select @maxdate = max(dato) from reports.daily d where d.ugedag = @uge
			update reports.daily set mersalg = b.xtrasalg from
				(select a.opsalg + b.xpsalg as xtrasalg from
				(select sum(liniebeløb)*0.4 as opsalg from quilldb.debfakpost where fakturadato between @mindate and @maxdate and mersalg = 'Opsalg') a,
				(select sum(liniebeløb) as xpsalg from quilldb.debfakpost where fakturadato between @mindate and @maxdate and (mersalg = 'Plussalg' or mersalg = 'Krydssalg')) b) b
			where day_of_date = 'mersalg' and ugedag = @uge 
			set @uge = @uge + 1
    	end

	update reports.daily set ugedag = year(@from_date)-1, sales = b.sales, cost = b.cost from
	(select sum(c.liniebeløb*b.kurs) +
	  ((select sum(b.gebyr*b.kurs) from quilldb.debfakjour b, (select c.fakturanummer
		from quilldb.debfakpost c where c.fakturadato between dateadd(year, -1, @from_date) and dateadd(year, -1, @to_date)
		and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
		group by c.fakturanummer) c where b.fakturanummer = c.fakturanummer)) as sales,
	  sum(c.kostværdi*b.kurs) as cost from quilldb.debfakjour b, quilldb.debfakpost c
	where b.fakturanummer = c.fakturanummer and
	c.fakturadato between dateadd(year, -1, @from_date) and dateadd(year, -1, @to_date)
	and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')) b
	where day_of_date = 'Last Year' 

		select @mthmin = reports.bestmth(@from_date, @to_date, month(@from_date))
		select @mthmax = dateadd(day, -1, dateadd(month, 1, @mthmin))

	update reports.daily set ugedag = month(@mthmin), sales = b.sales, cost = b.cost from
	(select sum(c.liniebeløb*b.kurs) +
	  ((select sum(b.gebyr*b.kurs) from quilldb.debfakjour b, (select c.fakturanummer
		from quilldb.debfakpost c where c.fakturadato between @mthmin and @mthmax
		and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')
		group by c.fakturanummer) c where b.fakturanummer = c.fakturanummer)) as sales,
	  sum(c.kostværdi*b.kurs) as cost from quilldb.debfakjour b, quilldb.debfakpost c
	where b.fakturanummer = c.fakturanummer and
	c.fakturadato between @mthmin and @mthmax
	and (c.varenummer <= '2021220' or (c.varenummer between '2100102' and '8809999') or (c.varenummer between '9900130' and '9900140') or c.varenummer >= '9999135')) b
	where day_of_date = 'Max måned'

update	reports.daily
set	dato = cast(day(dato) as nvarchar(4)) + '. ' + cast(datename(month, dato) as nvarchar(12))
where	dato <> ''

	select isnull(dato, ''), isnull(day_of_date, ''), isnull(ugedag, ''), isnull(replace(sales, '.', ','), '') as sales, isnull(replace(mersalg, '.', ','), '') as mersalg, isnull(callc, '') as callc, isnull(warehouse, '') as warehouse, isnull(replace(cost, '.', ','), '') as cost, isnull(replace(db, '.', ','), '') as db, isnull(replace(dg, '.', ','), '') as dg, isnull(replace(orderlines, '.', ','), '') as orderlines, isnull(replace(orders, '.', ','), '') as orders, isnull(replace(salesinvoice, '.', ','), '') as salesinvoice, isnull(replace(linesinvoice, '.', ','), '') as linesinvoice, isnull(replace(websales, '.', ','), '') as websales, isnull(replace(webpct, '.', ','), '') as webpct from reports.daily



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site