Forum Replies Created

Viewing 15 posts - 601 through 615 (of 1,228 total)

  • RE: Select Query - need optimization

    Sample data for testing:

    DROP TABLE Table_1

    CREATE TABLE Table_1(

    RowID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,

    custid INT,

    store VARCHAR(9) NOT NULL,

    amount INT)

    INSERT INTO Table_1 (custid, store, amount) -- 2 million...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 15, 2012 at 6:11 am

    #1433812

  • RE: Comparing data row by row....

    martin.david (1/15/2012)

    ChrisM@home (1/15/2012)

    Hi Martin

    By "sequence in data" - do you mean sequence by ID?

    Cheers

    Hi,

    i'm sorry, maybe i'm using wrong word for my problem..

    sequence that i mean is same transaction that...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 15, 2012 at 5:45 am

    #1433808

  • RE: Select Query - need optimization

    How many distinct stores are there in each table?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 15, 2012 at 1:29 am

    #1433782

  • RE: Comparing data row by row....

    Hi Martin

    By "sequence in data" - do you mean sequence by ID?

    Cheers

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 15, 2012 at 1:24 am

    #1433781

  • RE: Comparing data row by row....

    SQL Kiwi (1/14/2012)

    ChrisM@home (1/14/2012)

    Paul, have you compared the performance of this method against a rCTE yet? I must admit I should have but haven't taken the time out to do...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 2:40 pm

    #1433757

  • RE: Comparing data row by row....

    Paul, have you compared the performance of this method against a rCTE yet? I must admit I should have but haven't taken the time out to do it. If I...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 1:46 pm

    #1433753

  • RE: Select Query - need optimization

    SQL Kiwi (1/14/2012)

    ChrisM@home (1/14/2012)

    Edit: you've got a 700 million row sample data set on your home lappy? Good lord - I need an upgrade.

    No, I just ran:

    UPDATE STATISTICS dbo.Table_1

    WITH...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 11:01 am

    #1433720

  • RE: Select Query - need optimization

    Thanks Paul - looks like it's time to revisit Gail's NOT EXISTS vs etc blog 😉

    Edit: you've got a 700 million row sample data set on your home lappy? Good...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 10:44 am

    #1433717

  • RE: Select Query - need optimization

    In the meantime, you will get some mileage from this

    SELECT custid, store, Amount

    FROM Table_1 t1

    WHERE NOT EXISTS (SELECT 1 FROM table_2 t2 WHERE t2.store = t1.store AND t2.product !=...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 10:00 am

    #1433708

  • RE: Select Count from a big table

    Sharon-328725 (1/12/2012)

    Yes it has full text indes, but this query:

    SELECT Count(TBL_TOC.ID_TOC)

    FROM TBL_TOC

    WHERE (CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))

    takes 10 seconds to run...

    It's a very big table with 40,000,000 records!!

    Ten seconds ain't so bad.

    Can...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 9:40 am

    #1433705

  • RE: Comparing data row by row....

    Blimey! First post, and you've set up a ton of sample data! Top work!

    I think this is what you're looking for:

    SELECT

    Date,

    Price,

    AskClient,

    Dupes = COUNT(*)

    FROM #trading

    GROUP BY...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 14, 2012 at 9:33 am

    #1433703

  • RE: Help with Counts

    bicky1980 (1/13/2012)

    ...

    Thanks for the reply, if there were 2 duplicate datakeys with different landline values, I would only want this to be counted as one record, as I dont want...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 13, 2012 at 3:10 pm

    #1433561

  • RE: Help with Counts

    bicky1980 (1/13/2012)

    Total number of populated Landline fields (Needs to have a unique datakey as well as be unique itself - If the same landline is anywhere else in the ...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 13, 2012 at 2:04 pm

    #1433517

  • RE: Help with Counts

    Hi Bicky

    Some very experienced and talented folks are having real difficulty figuring out exactly what you want here, and it looks to me that you might still be figuring it...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 13, 2012 at 8:56 am

    #1433321

  • RE: Are the posted questions getting worse?

    Mark-101232 (1/13/2012)

    Can someone jump in here... seems like a simple problem made really complicated and I'm losing the will to live.

    http://www.sqlservercentral.com/Forums/Topic1234021-1292-1.aspx

    Mark - it looks like the OP is still evolving...

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

    January 13, 2012 at 8:49 am

    #1433311

Viewing 15 posts - 601 through 615 (of 1,228 total)