Search results

  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...
  16. I

    Optimizing a query with multiple LIKE '%abc%'

    It appears it's only one scan. Also it's much faster than I thought. I thought this was the bottle neck in my stored procedure but perhaps it's somewhere else.
  17. I

    Optimizing a query with multiple LIKE '%abc%'

    Does SQL Server scan the entire table 4 times with 4 LIKEs?
  18. I

    Optimizing a query with multiple LIKE '%abc%'

    Hello MS Access Forum, I have a stored procedure that relies on 4 LIKE '%abc%' statements. (See example code below) The table has ~850K records. Is there something that can be done to improve performance? Thank you for your advice. SELECT GENERATOR_ID FROM dbo.tblGenerators WHERE...
  19. I

    New to Microsoft Access world, should I invest the time learning it from scratch or go for the C#, SQL Server, and ASP path? Please advice

    That is true. Being comfortable with the complete development cycle in MS Access allowed me to transition to Blazor / SQL Server much more easily. I also have a mentor for the MudBlazor UI framework. Without him I probably wouldn't be able to get to a professional looking product.
  20. I

    New to Microsoft Access world, should I invest the time learning it from scratch or go for the C#, SQL Server, and ASP path? Please advice

    I transitioned to Microsoft’s Blazor for Web Development utilizing MudBlazor for the UI. It’s incredible for full stack development and with the help of AI very rapid development. There is about a 6 month learning curve before you get comfortable.
Back
Top Bottom