SQL Server HELP Required

[dineshns]

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.

[panayotisk]

Have a look on the articles of this site.
Do you use stored procedures?
Post some actual SQL code to search for improvements. I use temporary tables and they are not too much of a delay.

[dineshns]

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

[panayotisk]

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.

[dineshns]

That is a good idea.

Thank you very much panayotisk