Professional Web Applications Themes

Need help with repetitive Insert queries, help me consolidate... - Microsoft SQL / MS SQL Server

I am running 33 queries (inserts) from a table with about 20 million records. (I am parsing media URLs by filetype into separate tables) The 33 queries are nearly identical. (each a filetype and similar to below) Sample (2 inserts) : insert into dbAVI (filespec,name,heading) select distinct( 'http://' + C5) ,reverse(SUBSTRING(reverse(C5),1,CHARINDEX('/',reverse(C5))-1)),-1 from IA.dbo.dbIA where ( CHARINDEX('/',C5)>0 ) and (len(C5)<=247) and (C5 like '%.AVI') and not ('http://' + c5 in (select filespec from dbAVI where filespec='http://' +C5)) insert into dbMID (filespec,name,heading) select distinct( 'http://' + C5) ,reverse(SUBSTRING(reverse(C5),1,CHARINDEX('/',reverse(C5))-1)),-1 from IA.dbo.dbIA where ( CHARINDEX('/',C5)>0 ) and (len(C5)<=247) and (C5 like '%.MIDI' or C5 ...

  1. #1

    Default Need help with repetitive Insert queries, help me consolidate...

    I am running 33 queries (inserts) from a table with about 20 million
    records. (I am parsing media URLs by filetype into separate tables)
    The 33 queries are nearly identical. (each a filetype and similar to below)

    Sample (2 inserts) :

    insert into dbAVI (filespec,name,heading) select distinct( 'http://' + C5)
    ,reverse(SUBSTRING(reverse(C5),1,CHARINDEX('/',reverse(C5))-1)),-1 from
    IA.dbo.dbIA where ( CHARINDEX('/',C5)>0 ) and (len(C5)<=247) and (C5 like
    '%.AVI') and not ('http://' + c5 in (select filespec from dbAVI where
    filespec='http://' +C5))

    insert into dbMID (filespec,name,heading) select distinct( 'http://' + C5)
    ,reverse(SUBSTRING(reverse(C5),1,CHARINDEX('/',reverse(C5))-1)),-1 from
    IA.dbo.dbIA where ( CHARINDEX('/',C5)>0 ) and (len(C5)<=247) and (C5 like
    '%.MIDI' or C5 like '%.MID' or C5 like '%.RMI' ) and not ('http://' + C5 in
    (Select filespec from dbMID where filespec='http://' +C5))

    As you can see each of these 33 queries will scan the entire 20 million
    records and the process takes about 20 hours.
    Is there any way for it to scan once and put each type in its particular
    table while it runs...?

    If someone can pass me the general logic I can figure out the SQL to write,
    just with my limited knowledge I am unaware of any way to do what I
    described above.

    Any help greatly appreciated,
    -Craig


    Craig Guest

  2. #2

    Default Re: Need help with repetitive Insert queries, help me consolidate...

    Woops... the URL table should be...

    URL
    1 1 1 1 1

    And the query should also join the Server table as well....

    hehehehe too much wine and not enough SQL!

    "Terence Siganakis" <net.au> wrote in message
    news:3f28f832$adelaide.on.net... 
    normalised. 
    this... 
    even 
    > below) [/ref]
    C5) [/ref]
    like [/ref]
    C5) [/ref]
    like 
    > in 
    > write, 
    >
    >[/ref]


    Terence Guest

  3. #3

    Default Re: Need help with repetitive Insert queries, help me consolidate...

    Hi Craig,

    You can't insert into different tables with one insert statement, but you
    can improve the perforamance of the queries by using a temporary table:

    CREATE TABLE #files (file_type CHAR(3), filespec VARCHAR(400), name
    VARCHAR(400), heading VARCHAR(400), PRIMARY KEY (file_type, filespec))

    INSERT INTO #files (file_type, filespec, name, heading)
    select distinct RIGHT(C5,3),
    ( 'http://' + C5)
    ,reverse(SUBSTRING(reverse(C5),1,CHARINDEX('/',reverse(C5))-1)),-1 from
    IA.dbo.dbIA where ( CHARINDEX('/',C5)>0 ) and (len(C5)<=247)

    And then insert in all your separate tables from the temporary table.

    You can also make the temporary table a permanent table and move all the
    filespec, name and heading inforamtion from those 33 tables to this table.


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Craig Stadler" <com> wrote in message
    news:phx.gbl... 
    below) 
    in 
    write, 


    Jacco Guest

  4. #4

    Default Re: Need help with repetitive Insert queries, help me consolidate...

    Jacco,

    Maybe this additional info will help :
    1. media types have to be in separate tables.
    2. I use BCP to bring in the raw data from flat files with columns
    c1,c2,c3,c4,and c5 (c5 being the media url without th http:// prefix) so it
    has to be brought in this way.

    Essentially im trying to move the data from the raw bcp'ed db into each
    appropriate table.
    I see your example but some extensions are 2 letrers (.RA), some 3 (.MP2)
    and some 4 (.AIFF) wouldnt this pose a problem?

    Thanks for the help so far! :-)
    -Craig


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:%phx.gbl... 
    > below) [/ref]
    C5) [/ref]
    like [/ref]
    C5) [/ref]
    like 
    > in 
    > write, 
    >
    >[/ref]


    Craig Guest

  5. #5

    Default Re: Need help with repetitive Insert queries, help me consolidate...

    > I see your example but some extensions are 2 letrers (.RA), some 3 (.MP2) 

    You can get the extension with SELECT RIGHT(C5, CHARINDEX('.',
    REVERSE(C5))-1)

    The advantage of the method I proposed is that you only have to do 1 full
    table scan on that import table, and all the work that will be done on the
    temporary table will be quite quick because the clustered index that is
    created by the primary key.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Craig Stadler" <com> wrote in message
    news:phx.gbl... 
    it [/ref]
    you [/ref]
    table. 
    > > below) [/ref]
    > C5) [/ref][/ref]
    from [/ref]
    > like [/ref]
    > C5) [/ref][/ref]
    from [/ref]
    > like [/ref][/ref]
    C5 [/ref][/ref]
    million [/ref][/ref]
    particular 
    > > write, 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. repetitive pattern vector picture as background
    By grendizerus in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: July 11th, 08:50 AM
  2. Query of queries: insert into another datasource
    By Arielladog in forum Coldfusion Database Access
    Replies: 5
    Last Post: July 24th, 02:14 AM
  3. Repetitive white spaces in string
    By d.acha in forum Macromedia ColdFusion
    Replies: 2
    Last Post: June 22nd, 09:37 PM
  4. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  5. how to detect and block repetitive attacks
    By Chris Fortune in forum Linux / Unix Administration
    Replies: 5
    Last Post: August 3rd, 08:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139