Recent content by ions

  1. I

    Optimizing a query with multiple LIKE '%abc%'

    I have a demo on Friday going over if I am querying the data in a meaningful manner. That meeting will tell me a lot about how to structure indexes and what the user's find valuable. Thanks BlueSpruce and yes turning off logging has been a game changer in terms of disk space.
  2. I

    Optimizing a query with multiple LIKE '%abc%'

    Thanks Albert. Will definitely research full text index now with those stats.
  3. I

    Optimizing a query with multiple LIKE '%abc%'

    Thank you Albert. You convinced me. I will invest the time to learn full text indexing.
  4. I

    Optimizing a query with multiple LIKE '%abc%'

    Thanks for everyone's feedback. The original stored procedure is working well. I have one more sp below. It works great except when the user doesn't provide the State parameter. Then it takes around 12 seconds with one FWC. Otherwise it's always under 3 seconds. vwManifestLines has...
  5. I

    Optimizing a query with multiple LIKE '%abc%'

    This is from my local machine. My machine is almost 5 years old so I find performance is better on Azure. Note, I haven't deployed this specific stored procedure on Azure. That will happen in about 2 months.
  6. I

    Optimizing a query with multiple LIKE '%abc%'

    I can't believe how fast it is now. I posted a private YouTube video demonstrating the speed. The Generators table has 842,152 records. YouTube video demonstrating speed of Stored Procedure
  7. I

    Optimizing a query with multiple LIKE '%abc%'

    Interesting. Thank you for letting me know about this new recent feature.
  8. I

    Optimizing a query with multiple LIKE '%abc%'

    Minty you are right I can use it with my current Azure Database. AI was initially telling me I cannot. Why doesn't full text index come installed by default with the local SQL Server installation? I might explore full text index now.
  9. I

    Optimizing a query with multiple LIKE '%abc%'

    This is a US government dataset and it's possible QUANTITY_HAZ_KG will be NULL for all records in the Group. In that case I want to return 0 because the Front End is expecting a Decimal. That being said, I moved the IsNull check to the final Select so it's only done once and not in the...
  10. I

    Optimizing a query with multiple LIKE '%abc%'

    The production version will be going on Azure and I believe only the high cost tiers allow full text index. Also when I attempted to implement full text index on my local machine I got the message that this feature is not installed.
  11. I

    Optimizing a query with multiple LIKE '%abc%'

    Done. Thank you.
  12. I

    Optimizing a query with multiple LIKE '%abc%'

    Finally query. I am still in shock how fast it is with almost a million Generator records. CREATE OR ALTER PROCEDURE [dbo].[GenHeadersByGenQF] @Year INT = 2024, @UserInput VARCHAR(250) = 'Walmart Chicago' AS BEGIN SET NOCOUNT ON; ;WITH SearchTerms AS ( SELECT...
  13. I

    Optimizing a query with multiple LIKE '%abc%'

    State is indexed. However, it's possible that the user may not enter a State. I think separating it into 2 queries may add too much complexity? Every search I tried now runs under 3 seconds and my machine is not that powerful. I think I will search on the full State name because something...
  14. I

    Optimizing a query with multiple LIKE '%abc%'

    You are right the State is "NY" or "MI" and should be an =. No need to make that a LIKE at all. Thank you CJ
  15. I

    Optimizing a query with multiple LIKE '%abc%'

    The bottle neck was in other parts of my stored procedure. The speed is pretty good now (below 4 seconds). Below is the full stored procedure. Do you see any further efficiencies? I am really shocked how fast it is now. tblGenerators has 842,152 records and vwManifestLines has 4,164,527...
See more
Back
Top Bottom