Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: select into really faster

    Hello

    I need this really faster in mS SQL 2000


    User
    number (int)
    reportid (FK)

    report
    reportid (PK)
    Category (int)


    SELECT A, B, C, D INTO UserCopy FROM User
    WHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category)
    AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)



    can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category

    and the table user has a few millions rows

    Report.ReportId is a Primary key for User.reportid (FK)

    for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximum

    thank you for helping
    Last edited by anselme; 09-26-06 at 12:06.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    One problem i see is that you have to do a table scan of the USER table because of the NOT IN qualifier. How many USER.NUMBER values would fulfill an IN or WHERE qualifier?

    Also, the in statement requires a full table scan to group Report rows into category to then derive the maximum reportID. How many rows in the reportID table.

    Maybe have a table of permitted User.Number for this query, or an additional 1 char indexed column with 'Y' or 'N' for IncludeInUserCopy query.

    Since we don't have sample schema and data, these are stabs in the dark.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Separating this into a create table statement and a then an INSERT INTO will also minimise your headaches.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    i have updated the first post for additional infos

    yes pootle any code is welcome
    how and what can i create
    is it possible as stored procedure or view ?

    user and report are filled by another application and i cannot change it too much, and extra column 0/1 y/n cannot work, or i must first update 10000000 of rows

    thank you
    Last edited by anselme; 09-26-06 at 12:13.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    CREATE TABLE UserCopy 
    (A Int, 
    B VarChar(10), 
    C SmallDateTime, 
    D Bit)
     
    INSERT INTO UserCopy (A, B, C, D)
    SELECT A, B, C, D
    FROM User
    WHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) 
    AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)
    All tom's comments stand too of course. There are problems creating permanent tables like this (you sure this isn't supposed to be a temp table?) and you will need an existence check with a drop statement too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2005
    Posts
    266
    INSERT INTO UserCopy (A, B, C, D)
    SELECT A, B, C, D

    doesnt work, i am trying in MSSQL Server management studio and i get an error

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    INSERT INTO UserCopy (A, B, C, D)
    SELECT A, B, C, D

    doesnt work, i am trying in MSSQL Server management studio and i get an error
    Please post the error message...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    with INSERT INTO UserCopy (A, B, C, D)
    I GET ::

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ','.

    with INSERT INTO UserCopy A, B, C, D
    I GET ::
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'A'.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would bcp QUERYOUT and then BCP in the results

    But something doesn't seem right....this is NOT a btach process correct?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    with INSERT INTO UserCopy (A, B, C, D)
    I GET ::

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ','.

    with INSERT INTO UserCopy A, B, C, D
    I GET ::
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'A'.
    Can you paste the whole script, here line three(3) doesn't make sense...plz paste the whole script...I would like to see the line 3
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    Dec 2005
    Posts
    266
    the line 3 is that one
    with INSERT INTO UserCopy (A, B, C, D)
    i had 2 empty lines first

  12. #12
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    the line 3 is that one
    with INSERT INTO UserCopy (A, B, C, D)
    i had 2 empty lines first
    Okay,I think I have found your problem, just check for any comma in the line three(3) that you are not looking,may be its in the right side of that line where you have to scroll the horizontal bar to see that.

    That error comes when there is a comma in line 3 just like this

    Code:
    INSERT INTO UserCopy (A, B, C, D)               ,
    SELECT A, B, C, D
    FROM tt
    This will throw an error -
    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ','.

    Code:
    INSERT INTO UserCopy A, B, C, D              ,
    SELECT A, B, C, D
    FROM tt
    This will throw an error ---
    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near 'A'.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  13. #13
    Join Date
    Dec 2005
    Posts
    266
    ooops really sorry i am dum !

    it works !!!

    but not at all faster .. the same .. no more no less

    i dont know what to do , it returns 1000000 rows but > 2 minutes

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this method:
    Code:
    declare	@ExcludeList table
    	(Number int primary key clustered)
    insert into @ExcludeList
    select 120 union
    select 144 union
    select 206 union
    select 345 union
    select 221 union
    select 789 union
    select 548 union
    select 666 union
    select 1204 union
    select 4875 union
    select 22 union
    select 135 union
    select 777 union
    select 444
    
    SELECT	A,
    	B,
    	C,
    	D
    INTO	UserCopy
    FROM	[User]
    	left outer join @ExcludeList ExcludeList on [user].number = ExcludeList.Number
    	inner join --maxReports
    		(SELECT	MAX(report.reportID) AS maxReport
    		FROM	Report
    		GROUP BY report.Category) maxReports
    		on [User].reportid = maxReports.maxReport
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2005
    Posts
    266
    i 'll try it tomrrow morning

    thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •