SQL Server HELP Required
Dear ALL,
I am using SQL Server 2000 and Visual Basic. I need to generate an summary Report. The Data for the report are taken from 12 tables. I cant retriew data from a single SQL Statement. There are about 60,000 average records in each table.
I try to do it with temp Tables.
It was successfull. But when I generate report it seams to be very slow.
and also I saw in several wesites, "Dont use Temp Tables in SQL Server"
Is there any way of doing above.
In this kind of cases What can I Do?
Pl help me.
Thank you.
[583 byte] By [
dineshns] at [2007-11-19 10:54:44]
# 2 Re: SQL Server HELP Required
Thank for reply,
The code I use is as follows
CREATE PROCEDURE dbo.sp_Prepare_OutRegister_BorderTiles
@DateFrom smalldatetime,
@DateTo smalldatetime,
@FromLoc varchar(5),
@ToLoc varchar(10),
@Grade char(1)
AS
CREATE TABLE #T1(AutoNo bigint IDENTITY(1, 1) PRIMARY KEY,
REFNO varchar(10) NULL,
TDate smalldatetime,
Grade char(1) NULL,
QTY_1 bigint DEFAULT 0,
QTY_2 bigint DEFAULT 0,
QTY_D bigint DEFAULT 0,
QTY_P bigint DEFAULT 0,
QTY_Q bigint DEFAULT 0,
QTY_S bigint DEFAULT 0,
QTY_U bigint DEFAULT 0,
QTY_V bigint DEFAULT 0,
FromLocation varchar(5) NULL,
ToLocation varchar(10) NULL,
DocType varchar(3) NULL,
Category char(1) NULL)
DELETE FROM #T1
CREATE TABLE #T2(AutoNo bigint IDENTITY(1, 1) PRIMARY KEY,
REFNO varchar(10),
Grade char(1) NULL,
QTY bigint DEFAULT 0)
-- INSERT DISTINCT REF NO's --
INSERT INTO #T1(REFNO, TDate, Grade, FromLocation, ToLocation, DocType, Category)
SELECT DISTINCT REFNO, TDate, Grade, FromLocation, ToLocation, DocType, 'B' AS Category
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND (TDate <= @DateTo) AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Category = 'B') AND (Grade = @Grade)
-- INSERT 1 QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = '1') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_1 = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF 1
-- INSERT 2 QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = '2') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_2 = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF 2
-- INSERT D QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = 'D') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_D = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF D
-- INSERT P QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = 'P') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_P = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF U --
-- INSERT Q QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = 'Q') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_Q = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF Q
-- INSERT S QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = 'S') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_S = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF S
-- INSERT U QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = 'U') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_U = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF U --
-- INSERT V QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = 'V') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_V = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF V --
SELECT * FROM #T1
GO
# 3 Re: SQL Server HELP Required
I cannot test your code with actual data, I 'll just tell suggestions.
For the 1st part:
-- INSERT DISTINCT REF NO's --
INSERT INTO #T1(REFNO, TDate, Grade, FromLocation, ToLocation, DocType, Category)
SELECT DISTINCT REFNO, TDate, Grade, FromLocation, ToLocation, DocType, 'B' AS Category
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND (TDate <= @DateTo) AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Category = 'B') AND (Grade = @Grade)
-- INSERT 1 QTY --
INSERT INTO #T2(REFNO, Grade, QTY)
SELECT REFNO, Grade, SUM(Qty) AS QTY
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND
(TDate <= @DateTo) AND
(Category = 'B') AND (ItemSize = '1') AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Grade = @Grade)
GROUP BY REFNO, Grade
UPDATE #T1
SET QTY_1 = tt.QTY
FROM #T1 t, #T2 tt
WHERE (t.REFNO = tt.REFNO) AND
(t.Grade = tt.Grade)
DELETE FROM #T2
-- END OF 1
I think (I have not tested anything) that you do not actually need the 2nd temp table. How about smthng like
INSERT INTO #T1(REFNO, TDate, Grade, FromLocation, ToLocation, DocType, Category, QTY_1)
SELECT DISTINCT REFNO, TDate, Grade, FromLocation, ToLocation, DocType, Category, SUM(Qty)
FROM vwOUTDetails_3
WHERE (TDate >= @DateFrom) AND (TDate <= @DateTo) AND
(FromLocation = @FromLoc) AND (ToLocation = @ToLoc) AND
(Category = 'B') AND (Grade = @Grade)
GROUP BY REFNO, Grade
HAVING ItemSize = '1'
Each time you change ItemSize if I get it right. There may be a way to write all these with one query. Someone who is strong with SQL may help here. I'll check it later and see if I can come up with only one query.
