Forum Replies Created

Viewing 15 posts - 241 through 255 (of 7,626 total)

  • Reply To: 3rd Friday of the Month

    It would be simpler and more efficient to just calc the 3rd Friday of the month and then see if there are any rows for that date, i.e., you don't...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 19, 2024 at 5:57 am

    #4397915

  • Reply To: Rebuilding/Reorganizing in a very transactional table

    I don't see why you'd need more then 150GB of RAM for a 31GB table.  If that's "the big table" in the db, presumably the entire is only 50 or...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 17, 2024 at 3:59 am

    #4396783

  • Reply To: Database Ownership - Does it Matter ?

    Agreed.  You really want to have all the dbs having the same owner.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 15, 2024 at 8:58 pm

    #4396136

  • Reply To: Query wont use any index - Cannot understand why

    We would need DDL for the tables and some cardinality info (how many rows for each condition).

    But, overall, IF that is a consistent query pattern, you should cluster the table...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 14, 2024 at 3:02 pm

    #4395332

  • Reply To: incremental load

    I prefer the 2nd approach, but for the 1st approach:

    The processed_flag should be a bit, 0/1, rather than a full char (y/n).  If you already have a bit column(s) in...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 12, 2024 at 3:37 pm

    #4394076

  • Reply To: find the number of values less than the current row

    Need sample data that is directly usable, that is, CREATE TABLE and INSERT statement(s), not just a splat on the screen.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 8, 2024 at 9:57 pm

    #4392117

  • Reply To: how to speed this query up?

    D'OH, sorry, that should be:

    CAST(GETDATE() AS date)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 8, 2024 at 2:06 pm

    #4391922

  • Reply To: how to speed this query up?

    You've already got restrictions based on @LOOK_BACK_PERIOD in the WHEREs, it would just be more efficient to pre-filter rather than waiting until some processing has been done on all the...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 7, 2024 at 8:41 pm

    #4391505

  • Reply To: how to speed this query up?

    Did you add the WHERE condition for the TXN_DATE?  If you have a long history in the table, that would help the most.

    Then try an index on (txn_date, company) include...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 7, 2024 at 6:17 pm

    #4391436

  • Reply To: how to speed this query up?

    water490 wrote:

    ScottPletcher wrote:

    Need the DDL for the table to sure of more specific recommendations for best speed, but, assuming you will keep a long history in this table:

    (1) Either: (A)...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 7, 2024 at 4:51 pm

    #4391390

  • Reply To: how to speed this query up?

    Need the DDL for the table to sure of more specific recommendations for best speed, but, assuming you will keep a long history in this table:

    (1) Either:

    (A) cluster the table...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 7, 2024 at 3:52 pm

    #4391355

  • Reply To: Pre-defined filters for stored procedures

    I've done similar set ups.  Dynamic SQL is definitely a good choice here.  To keep the FilterAttribute column consistent, I suggest you use a different column to control include or...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 6, 2024 at 3:55 pm

    #4390733

  • Reply To: SQL server disk block size formatted as 4KB runs slow

    The *data* file drives should be 64K.  Log file drives should still be 4K.  If you mix data and log files on the same drive, you'll want to use 64K.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 6, 2024 at 3:52 pm

    #4390731

  • Reply To: how to filter out zeros when calculating average 


    SELECT T3.SYMBOL,
    T3.TRADE_DATE,
    (AB30_C + AB30_P + AB30_G + AB30_R + AB30_Y) / NULLIF(SUM(CASE WHEN AB30_C = 0 THEN 0 ELSE 1 END + CASE WHEN AB30_P = 0...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 5, 2024 at 9:48 pm

    #4390215

  • Reply To: Indexing strategy very long text strings

    ScottPletcher wrote:

    I would think you would want four ids:

    drive full path (other than drive, of course) filename extension

    When new data comes in, you'd pre-add any new ids before loading the...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    March 4, 2024 at 8:12 pm

    #4389563

Viewing 15 posts - 241 through 255 (of 7,626 total)