SELECT INTO

SELECT INTO

Post by Carlos Hernande » Wed, 29 May 2002 00:10:06



I have these three tables:

Table 1: Inventory Listing
    Fields:   Code

Table 2: Inventory Detail
   Fields:    Code
                 DocNumber
                 DocType
                 Date
                 UnitStock

The actual data on table 1 contains 38,000 Records
The actual data on table 2 contains 7,000 records, and is starting to get
bigger every day.

Table 2 has all the information from each Product. It contains every
transaction.
In the field UnitStock it contains the actual stock for that day. It is
calculated with a special procedure.
The problem is that I want to get a list of all products with their stock in
an specific date.

The query I came up is this one:

SELECT Code,(SELECT TOP 1 UnitStock FROM Table2 T2 WHERE T2.code=T1.Code and
Date<="2002-01-31" ORDER BY Date DESC,DocType DESC,DocNumber DESC) as Units
FROM Table1 T1

This query takes too much time, like 30 to 40 minutes. Too much.
99% of the time is wasted in the ORDER BY Clause. But I can't take it out.
It is necessary.

So I came up with another Query.

First I SELECT INTO Table2 into a Temporary table with the recordse in the
order I need them

SELECT Code,Date,UnitStock INTO ##TempTable FROM Table2 T2 ORDER BY Date
DESC,DocType DESC,DocNumber DESC

This query takes about 2 seconds!!

Now this is the query that gives me my result

SELECT Code,(SELECT TOP 1 UnitStock FROM ##TempTable TT WHERE
TT.code=T1.Code and Date<="2002-01-31") as Units FROM Table1 T1

This query takes about 1 seconds!!

First I want to know How can I make the first option very fast without using
a Temporary Table.

My second question is: If I have to use a temporary table, SELECTING INTO a
Temp table a set of data in an ordered way guarantees me that getting the
data from the temp table will be ordered.

I am using Sql Server 7.0

Thanks in advance.

 
 
 

SELECT INTO

Post by Robert Elli » Wed, 29 May 2002 03:44:01


Carlos,

If you post the script to create the tables I would be happy to take a look
at how you can optimise this. Pls script the table indexes as well, you can
select that option in the Generate Scripts dialog.

--
--
Kind Regards,

Robert A. Ellis, MCSD
Software Developer


Quote:> I have these three tables:

> Table 1: Inventory Listing
>     Fields:   Code

> Table 2: Inventory Detail
>    Fields:    Code
>                  DocNumber
>                  DocType
>                  Date
>                  UnitStock

> The actual data on table 1 contains 38,000 Records
> The actual data on table 2 contains 7,000 records, and is starting to get
> bigger every day.

> Table 2 has all the information from each Product. It contains every
> transaction.
> In the field UnitStock it contains the actual stock for that day. It is
> calculated with a special procedure.
> The problem is that I want to get a list of all products with their stock
in
> an specific date.

> The query I came up is this one:

> SELECT Code,(SELECT TOP 1 UnitStock FROM Table2 T2 WHERE T2.code=T1.Code
and
> Date<="2002-01-31" ORDER BY Date DESC,DocType DESC,DocNumber DESC) as
Units
> FROM Table1 T1

> This query takes too much time, like 30 to 40 minutes. Too much.
> 99% of the time is wasted in the ORDER BY Clause. But I can't take it out.
> It is necessary.

> So I came up with another Query.

> First I SELECT INTO Table2 into a Temporary table with the recordse in the
> order I need them

> SELECT Code,Date,UnitStock INTO ##TempTable FROM Table2 T2 ORDER BY Date
> DESC,DocType DESC,DocNumber DESC

> This query takes about 2 seconds!!

> Now this is the query that gives me my result

> SELECT Code,(SELECT TOP 1 UnitStock FROM ##TempTable TT WHERE
> TT.code=T1.Code and Date<="2002-01-31") as Units FROM Table1 T1

> This query takes about 1 seconds!!

> First I want to know How can I make the first option very fast without
using
> a Temporary Table.

> My second question is: If I have to use a temporary table, SELECTING INTO
a
> Temp table a set of data in an ordered way guarantees me that getting the
> data from the temp table will be ordered.

> I am using Sql Server 7.0

> Thanks in advance.


 
 
 

SELECT INTO

Post by David Kyl » Thu, 30 May 2002 08:29:16


you probably just need to put an index on Date, DocType, DocNumber.  (make
them Descending indexes)
that will most likely fix the problem (I think).


> Carlos,

> If you post the script to create the tables I would be happy to take a
look
> at how you can optimise this. Pls script the table indexes as well, you
can
> select that option in the Generate Scripts dialog.

> --
> --
> Kind Regards,

> Robert A. Ellis, MCSD
> Software Developer



> > I have these three tables:

> > Table 1: Inventory Listing
> >     Fields:   Code

> > Table 2: Inventory Detail
> >    Fields:    Code
> >                  DocNumber
> >                  DocType
> >                  Date
> >                  UnitStock

> > The actual data on table 1 contains 38,000 Records
> > The actual data on table 2 contains 7,000 records, and is starting to
get
> > bigger every day.

> > Table 2 has all the information from each Product. It contains every
> > transaction.
> > In the field UnitStock it contains the actual stock for that day. It is
> > calculated with a special procedure.
> > The problem is that I want to get a list of all products with their
stock
> in
> > an specific date.

> > The query I came up is this one:

> > SELECT Code,(SELECT TOP 1 UnitStock FROM Table2 T2 WHERE T2.code=T1.Code
> and
> > Date<="2002-01-31" ORDER BY Date DESC,DocType DESC,DocNumber DESC) as
> Units
> > FROM Table1 T1

> > This query takes too much time, like 30 to 40 minutes. Too much.
> > 99% of the time is wasted in the ORDER BY Clause. But I can't take it
out.
> > It is necessary.

> > So I came up with another Query.

> > First I SELECT INTO Table2 into a Temporary table with the recordse in
the
> > order I need them

> > SELECT Code,Date,UnitStock INTO ##TempTable FROM Table2 T2 ORDER BY Date
> > DESC,DocType DESC,DocNumber DESC

> > This query takes about 2 seconds!!

> > Now this is the query that gives me my result

> > SELECT Code,(SELECT TOP 1 UnitStock FROM ##TempTable TT WHERE
> > TT.code=T1.Code and Date<="2002-01-31") as Units FROM Table1 T1

> > This query takes about 1 seconds!!

> > First I want to know How can I make the first option very fast without
> using
> > a Temporary Table.

> > My second question is: If I have to use a temporary table, SELECTING
INTO
> a
> > Temp table a set of data in an ordered way guarantees me that getting
the
> > data from the temp table will be ordered.

> > I am using Sql Server 7.0

> > Thanks in advance.

 
 
 

1. Select into #temptable and select from #temptable error :

Hi Freinds,
SQL 2000

CREATE PROCEDURE test
AS

SELECT
te.ntimecardid  , vl.cbranchsystemid, vl.cbranchcorpid, a.cpayrollid,
app.cssn, convert(char(10),te.dweekend,101) as dweekend
, sum(te.nregular) as nsumregular, te.npayrate, te.nbillrate
,' ' as NBCODE1, sum(te.novertime) as nsumovertime, te.notpayrate,
te.notbillrate
,' ' as NBCODE2, sum(te.ndoubletime) as nsumdoubletime, te.ndtpayrate,
te.ndtbillrate
,' ' as NBCODE3, te.nmisctypeid
,(case when cp.cpaytype = 'H' then sum(te.nmischours) when cp.cpaytype = 'D'
then sum(te.nmiscamount) else 0 end) as nsummischourunit
, te.npayrate AS nmiscpayrate, te.nbillrate AS nmiscbillrate
,' ' as NBCODE4
,(SELECT g.coacode FROM clientlocationsbcode c inner join globalspecialbill
g on c.csbcode = g.csbcode WHERE c.norder = 1 and c.nclientlocationid =
th.nclientlocationid) as SPB1
, te.cudf1
,(SELECT g.coacode FROM clientlocationsbcode c inner join globalspecialbill
g on c.csbcode = g.csbcode WHERE c.norder = 2 and c.nclientlocationid =
th.nclientlocationid) as SPB2
, te.cudf2
,(SELECT g.coacode FROM clientlocationsbcode c inner join globalspecialbill
g on c.csbcode = g.csbcode WHERE c.norder = 3 and c.nclientlocationid =
th.nclientlocationid) as SPB3
, te.cudf3
,'ASSGN' as SPB4
, th.nassignmentid as cudf4
,' ' as SPB5 , ' ' as cudf5
,' ' as SPB6 , ' ' as cudf6
,' ' as SPB7 , ' ' as cudf7
,' ' as SPB8 , ' ' as cudf8
,' ' as SPB9 , ' ' as cudf9
,' ' as SPB10 , ' ' as cudf10
,(case when vl.lbillonly = 1 then '1' else '0' end) as lbillonly
into #tmp1
FROM timecardentry te
INNER JOIN timecardheader th ON te.ntimecardid = th.ntimecardid
INNER JOIN assignment a ON th.nassignmentid = a.nassignmentid
INNER JOIN vendorlocation vl ON th.nvendorlocationid = vl.nvendorlocationid
INNER JOIN applicant app ON th.napplicantid = app.napplicantid
LEFT OUTER JOIN clientpaycode cp ON cp.nclientpaycodeid = te.nmisctypeid
group by cp.cpaytype,vl.cbranchsystemid,  vl.cbranchcorpid, a.cpayrollid,
app.cssn, te.dweekend
, te.npayrate,te.nbillrate, te.notpayrate, te.ndtpayrate, te.notbillrate,
te.ndtbillrate, te.nmisctypeid
, te.cudf1, te.cudf2, te.cudf3, vl.lbillonly,th.nvendorlocationid ,
th.nclientlocationid, th.nassignmentid , te.ntimecardid
order by app.cssn, te.dweekend

select * from #tmp1  ---- OK
SELECT dweekend  FROM #tmp1   ---- OK
SELECT ntimecardid  FROM #tmp1   ---- ERROR

ERROR THAT I GET :

Server: Msg 207, Level 16, State 3, Procedure test, Line 60
Invalid column name 'ntimecardid'.

Any Idea why? colimn is there but select can't find it !!!!!

Thanks in advance,
Pat

2. suspect db

3. SELECT DISTINCT blocks; SELECT ALL doesn't

4. pg_depend

5. SQL Select From Select Statement

6. Dimension trouble....

7. select * or select column_names

8. Using large amount of memory

9. vb Select * from tableA?How to select record from 1000 to 2500

10. Select nth item in a select list...

11. Conditional SELECT within SELECT???

12. Selecting from a select

13. Parameters in SELECT Clause (SELECT ? AS X)