• Skip to main content
  • Skip to header right navigation
  • Skip to site footer
Foster Solutions (FSI)

Foster Solutions (FSI)

The Solution is in the Process

  • Home
  • What We Do
    • Websites / Hosting
      • Author’s Sites
      • Boutique Websites
      • Corporate Websites
      • Open Source CMS
      • Quick and Dirty Websites
    • SQL Databases / VBA Automation
    • GIS / Market Research
    • Government/Planning
  • Case Studies
    • Databases/Automation Case Studies
    • GIS/Market Research Case Studies
    • Local Government Case Studies
    • FSI – crosspollination – examples
  • Clients
  • Articles
  • Contact Us

SQL Tricks: helping functions-use your temp tables

When I first found out about functions in SQL Server, I thought it would be a silver bullet to modularize my code. In the end, I realized a strategy was necessary to prevent performance degradation. One helpful approach is to use temp tables.

When I first found out about functions in SQL Server, I thought it would be a silver bullet to modularize my code.

In the end, I realized performance suffers greatly, and functions need to be used sparingly, and sql needs to be written in a way to get around the performance hit.

For example, I could set up a segment of customers (“Cool People”)  in a database and have all my procedures to refer to my function to pull the “cool” customers.  The actual definition and/or list of cool customers may change, but I only need to change it in one place and it will disseminate through all my code.

BUT, if you are dealing with complex queries or larger data sets, then you’ll be surprised how significantly it will slow down your procedures.

If i nest “where customer_id in (select * from udf_coolPeople)”  and that is part of a complex query.. watch out and wait..

One strategy is to create a temp table that lists the cool people, so this approach should perform just fine for both scalar and table based functions:

CREATE TABLE #T_COOL (customer_id varchar(30))
Insert into #T_COOL  SELECT * FROM udf_coolPeople
select * from tblProducts where customer_id in (select customer_id from #T_COOL)

I don’t know all the workings of the engine, I just drive the car a LOT and have found best practices through trial and error. But the main problem is that SQL has a very intelligent approach and builds a query engine plan, and the function is like a black box and doesn’t offer any visibility.

Ready to get started?

Client Survey

Please take this survey if you are interested in a new or modified website. We will assess and get back with you as soon as possible.


Finding Solutions

We work with you to make things faster, easier and better.

Sites

  • Home
  • Blog
  • About
  • Features
  • Contact
  • Marketing

Features

  • Home
  • Blog
  • About
  • Features
  • Contact
  • Marketing

Support

  • Home
  • Blog
  • About
  • Features
  • Contact
  • Marketing

Copyright © 2022 · Foster Solutions (FSI) · All Rights Reserved

Back to top