July 10, 2018 at 2:46 am
I have Customers table (normally no more than 6,000 records as we are B2B) and I am writing a new invoice generation process using OOXML (the old one uses Word Interop).
Customers table has various columns, which appear on different parts of an invoice, e.g. Customer's name and address are at the header of the invoice, but some other columns from this
table are Boolean to decide what to show and what to hide on the invoice, e.g. show / hide VAT or which language to use (English, French).
With an old approach I have called the table once with all the columns I need and then stored them locally and processed, but with the new approach it will be easier from a development point
of view (it is out of the scope here, hence will not go into the details) to query Customers once to obtain customer's name and address and only later to query for all the invoice related settings.
My question is what would be the best approach in terms of SQL performance, please:
1) to query Customer table twice, as it will be still cached anyhow by SQL server;
2) to insert Customer data into session temporary table with all the columns I will need;
3) to continue with my old approach of having a local object, e.g. C# DataTable an populate with all the columns from Customers table I will need?
Note: neither name, address nor Boolean columns are part of any index
Much appreciated, as always.
July 10, 2018 at 6:28 am
BOR15K - Tuesday, July 10, 2018 2:46 AMI have Customers table (normally no more than 6,000 records as we are B2B) and I am writing a new invoice generation process using OOXML (the old one uses Word Interop).
Customers table has various columns, which appear on different parts of an invoice, e.g. Customer's name and address are at the header of the invoice, but some other columns from this
table are Boolean to decide what to show and what to hide on the invoice, e.g. show / hide VAT or which language to use (English, French).With an old approach I have called the table once with all the columns I need and then stored them locally and processed, but with the new approach it will be easier from a development point
of view (it is out of the scope here, hence will not go into the details) to query Customers once to obtain customer's name and address and only later to query for all the invoice related settings.My question is what would be the best approach in terms of SQL performance, please:
1) to query Customer table twice, as it will be still cached anyhow by SQL server;
2) to insert Customer data into session temporary table with all the columns I will need;
3) to continue with my old approach of having a local object, e.g. C# DataTable an populate with all the columns from Customers table I will need?Note: neither name, address nor Boolean columns are part of any index
Much appreciated, as always.
Create a properly optimised stored procedure, with whatever parameters are necessary, to return all of the data you need, in one hit. (You have not explained why you do this twice, but perhaps there is a good reason.)
Call that from C#, to populate your DataTable & continue as before ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 10, 2018 at 7:00 am
BOR15K - Tuesday, July 10, 2018 2:46 AMI have Customers table (normally no more than 6,000 records as we are B2B) and I am writing a new invoice generation process using OOXML (the old one uses Word Interop).
Customers table has various columns, which appear on different parts of an invoice, e.g. Customer's name and address are at the header of the invoice, but some other columns from this
table are Boolean to decide what to show and what to hide on the invoice, e.g. show / hide VAT or which language to use (English, French).With an old approach I have called the table once with all the columns I need and then stored them locally and processed, but with the new approach it will be easier from a development point
of view (it is out of the scope here, hence will not go into the details) to query Customers once to obtain customer's name and address and only later to query for all the invoice related settings.My question is what would be the best approach in terms of SQL performance, please:
1) to query Customer table twice, as it will be still cached anyhow by SQL server;
2) to insert Customer data into session temporary table with all the columns I will need;
3) to continue with my old approach of having a local object, e.g. C# DataTable an populate with all the columns from Customers table I will need?Note: neither name, address nor Boolean columns are part of any index
Much appreciated, as always.
I can't come up with a reason to NOT index the table for performance. Why waste even one nanosecond doing things that don't need to be done? Unless you're worried about a hack gaining access to your C# data table, and/or your SQL session, I can't come up with a reason to not use that technique either. However, I'm no web security expert.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2018 at 8:14 am
100% agree with Phil. Get everything in one read and save/cache it for your app however you need to. Db read round trips (from your app to the db server and back) are relatively expensive, so do as few as you reasonably can.
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".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply