November 14, 2017 at 8:33 am
Hi,
I have the following temp table listed below.
In the interface we created the user has the option to enter in search criteria in any one of the following fields
VendorPartNumber
ManufacturerPartNumber
UPCCode
ShortDescription
EditStatus
What index(es) would you recommend configuring?
CREATE TABLE #ChangeProducts (
TempKey [decimal](18, 0) NOT NULL IDENTITY(1,1) PRIMARY KEY,
CatalogueKey [decimal](18, 0) NULL,
CatalogueProductKey [decimal](18, 0) NULL,
EditStatus [int] NOT NULL,
EffectiveStartDate [date] NOT NULL,
EffectiveEndDate [date] NOT NULL,
SupplierKey [decimal](18, 0),
VendorPartNumber [nvarchar](50) NOT NULL,
BuyerPartNumber [nvarchar](50) NOT NULL,
ManufacturerPartNumber [nvarchar](50) NOT NULL,
ShortDescription [nvarchar](255) NULL,
Description [nvarchar](750) NOT NULL,
ConversionFactor[decimal](18, 5),
UPCCode [nvarchar](MAX) NOT NULL,
UnitPrice[decimal](18, 5),
RetailPrice[decimal](18, 5),
UOM [nvarchar](50) NOT NULL,
QuantityUOM [nvarchar](50) NOT NULL,
MinimumOrderQuantity[decimal](18, 5) NULL,
OrderMultiple[decimal](18, 5),
Dimensions [nvarchar](50) NOT NULL,
Brand [nvarchar](50) NULL,
Department [nvarchar](50) NOT NULL,
Size [nvarchar](4000) NOT NULL,
Colour [nvarchar](50) NOT NULL,
Class [nvarchar](50) NOT NULL,
SubClass [nvarchar](50) NOT NULL,
Keywords [nvarchar](500) NOT NULL,
MinAdvertisedPrice [decimal](18,5),
WholesalePrice [decimal](18,5),
ModifierTitle [nvarchar](100),
ModifierData [nvarchar](MAX),
ExtraInfo [nvarchar](MAX) NOT NULL,
ImageThumb [nvarchar](100) NOT NULL,
ImageSmall [nvarchar](100) NOT NULL,
ImageMedium [nvarchar](100) NOT NULL,
ImageLarge [nvarchar](100) NOT NULL,
SupplierWebsite [nvarchar](300) NULL,
ItemColorCode [nvarchar](10),
VendorStyleCode [nvarchar](30),
ItemClassCode [nvarchar](30),
Gender [nvarchar](30),
Age [nvarchar](30),
FEDASCode [nvarchar](30),
CountryOfOrigin [nvarchar](30),
TransportationTermsCode [nvarchar](30),
SeasonCode [nvarchar](30),
ModelNumber [nvarchar](30),
ItemMultiplier [decimal](18, 0) NULL,
ExpShipDate [date] NULL,
ExpShipEndDate [date] NULL,
Rebateable [int] NOT NULL,
SortOrder [int] NULL,
Hierarchy1 [nvarchar](2000) NULL,
Hierarchy2 [nvarchar](2000) NULL,
Hierarchy3 [nvarchar](2000) NULL,
Hierarchy4 [nvarchar](2000) NULL,
Hierarchy5 [nvarchar](2000) NULL,
SizingGridName [nvarchar](100) NULL,
GTIN [nvarchar](50) NULL,
CSQ [nvarchar](50) NULL,
GroupBuildSKU [nvarchar](50) NULL,
LBMXGuidId [nvarchar](100) NULL,
AzureGUID [nvarchar](50) NULL
)
November 14, 2017 at 9:14 am
Indexing of the usual kind may not help a lot, unless you limit the search capability to always involve characters at the beginning of the string. If that were the case, then you can index each of the searchable fields, but again, that means that finding a value that occurs in the middle of a given column will not be able to make use of such an index, as every such search would have to read every row of the table every time. You'll need to be a lot more specific about what kind of searching you are looking to do. FULL TEXT INDEXING might be an alternative, as then you can use a CONTAINS search instead of just LIKE, but that requires more knowledge than I have. You should be able to find out what's involved with a Google search.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 14, 2017 at 9:21 am
Good point Steve...
We do limit the searching to only strings that start with what the user inputted.
So if the user entered "bob" we turn it into like "bob%"
Wondering if that makes a difference in the answer..
November 14, 2017 at 9:28 am
tfeuz - Tuesday, November 14, 2017 9:21 AMGood point Steve...We do limit the searching to only strings that start with what the user inputted.
So if the user entered "bob" we turn it into like "bob%"Wondering if that makes a difference in the answer..
Absolutely. You can then have an index on each of the searchable columns, and you can use the LIKE operator with a trailing % and actually still get good performance. However, without a heck of a lot more information on how else that table is used, any other indexes would be entirely guesswork.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 14, 2017 at 9:32 am
That is actually the only way it is used... the user is just doing a search on the table and they have an option of searching by any of the 5 search fields.
November 14, 2017 at 9:47 am
tfeuz - Tuesday, November 14, 2017 9:32 AMThat is actually the only way it is used... the user is just doing a search on the table and they have an option of searching by any of the 5 search fields.
Then those 5 indexes should be sufficient. Be sure to INCLUDE any other columns that need to be returned in your query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 14, 2017 at 9:51 am
Here is the problem with the include ---
the gird that returns the results is configurable by user, they can choose to have as little as one of the columns in the table and as many as all of the columns.
Thoughts?
November 14, 2017 at 10:19 am
tfeuz - Tuesday, November 14, 2017 9:51 AMHere is the problem with the include ---
the gird that returns the results is configurable by user, they can choose to have as little as one of the columns in the table and as many as all of the columns.Thoughts?
Well, that does pose some problems. If you have plenty of disk space to spare, you could include ALL the columns for each index, but understand that means you effectively have 6 copies of ALL your data - the original, and the 5 indexes. Then there's the sizable overhead on any INSERT to that table. I'd generally recommend limiting which fields they can get to. Just doesn't seem practical to offer all of them up. Alternatively, you might be able to come up with some specific combinations of columns that are most commonly used, and only include those in the indexes. Then when they choose a column that is not indexed, the query will encounter a key lookup, which, depending on the number of rows they return, may or may not be problematic. I would resist including the nvarchar(2000) columns in any of the indexes.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 15, 2017 at 1:22 am
Can you post an example query that's run for this search?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2017 at 3:25 am
Why is this a temp table?
Is it a "materialized" view (join) of several separate tables?
If it's the case, then you's probably better querying joined tables directly.
Assuming, of course, those tables are indexed appropriately.
_____________
Code for TallyGenerator
November 15, 2017 at 5:52 am
Gail,
Most of queries we are seeing are:
select * from #ChangeProducts where vendorpartnumber like 'bob%'
As noted above, the user has 5 fields to search on - the one they use the most is vendor part number.
As for the "select *" - our users are given the choice of selecting which columns they want to see in the grid. For a number of reasons that I won't get into, and quite honestly don;t really understand, a significant amount of user choose to include all the columns.
November 15, 2017 at 5:55 am
We move all of the products out of the main table into a temp table for the user session (while they are dealing with the products in a catalogue) for a number of performance and usability reasons.
Our testing has proved to us that we can get much better performance and a lot less locking\deadlock related issues when we move the data temporarily out of the main large table
Tony
November 15, 2017 at 7:08 am
Are you trying to tell that the query
INSERT INTO #ChangeProducts ()
SELECT ...
FROM Joined Tables
With no WHERE filter
Performs better and applies less intensive locking than
SELECT ...
FROM Joined Tables
WHERE PartNumber = @SomeValue
?
I'd strongly recommend instead of optimising indexing on a temp table pay more attention to the indexing on the background tables.
Thats what will fix your performance for real.
_____________
Code for TallyGenerator
November 15, 2017 at 7:39 am
You could consider creating the clustered index on one of the columns you are querying against. As it stands it is almost certainly on TempKey (i.e. the primary key) which is not used assuming as you say these queries are the only way the table is used.
November 15, 2017 at 7:58 am
Segiy,
That is not quite what I am saying...
In the initial application I had a query that did the following:
select * from MainProduct Table where vendorpartnumber like 'bob%'
A few things about this:
- note, this is not the temp table
- this table contains millions of rows
- much like noted above, the "*" can be replaced with "whatever columns the user chooses to see"
- The where clause and include up to 5 distinct fields (they were listed in an earlier post)
What we found was that while we had one user querying the table and other users updating the table we were subject to deadlock issue and general slowdowns.
Additionally, because each user had different columns in the "select" and used different combinations in the "where" clause we found it difficult to add all the indexes, get the performance correct for both reads and adds\updates.
After some experimentation we discovered if we took the products that a user was working on (only one user can work on that set at once) and took the hit of moving the subset to a temp table (#ChangeProducts) we found that it was a better experience for the users. We are essentially allowing the users to "play" in their own sandbox and then write the final set of changes back to the main table when they were truly ready to commit.
It is absolutely possible we missed the mark and came up with a "kludge" and really we should address the issue in the main table. I am just not sure.
Tony
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply