OpenXML Identity Insert: overview

To start with, I'll give a simplified overview of my data.

BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc

OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data

ProblemTable (4mil rows)
ID | ConcatenatedHistory

The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'

Using left outer joins (all from base, the rest optional) I've got a
view something like:

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58;

But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58
and ConcatenatedHistory like '%test%' ;

What puzzles me most is that it's taking longer to return nothing.

I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.

The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code

Quote:

Originally Posted by

>From View inner join History on History.ID = View.ID

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.

~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.

If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.On Feb 8, 4:05 pm, "Merennulli" <mar...@.sdf.lonestar.orgwrote:

Quote:

Originally Posted by

To start with, I'll give a simplified overview of my data.
>
BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc
>
OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data
>
ProblemTable (4mil rows)
ID | ConcatenatedHistory
>
The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'
>
Using left outer joins (all from base, the rest optional) I've got a
view something like:
>
View (4mil rows)
ID | Name | Region | etc | Data | Data2 | ConcatenatedHistory
>
Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View
>
Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;
>
Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View
>
Where Region = 58;
>
But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View
>
Where Region = 58
and ConcatenatedHistory like '%test%' ;
>
What puzzles me most is that it's taking longer to return nothing.
>
I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.
>
The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.
>
Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code>From View inner join History on History.ID = View.ID
>
Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';
>
I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.
>
~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.
>
If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.

do you query the concatenated data, "normalized", in a way that you
could use FULL TEXT ?|||I expect that the normalized approach can perform much better than parsing
ConcatenatedHistory using LIKE as long as you have the proper indexes in
place and tune your queries. However it's difficult to make recommendations
without the actual DDL of your existing objects.

Quote:

Originally Posted by

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code

Quote:

Originally Posted by

>>From View inner join History on History.ID = View.ID

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

You might be able to reformulate this query as something like the example
below:

SELECT View.ID, View.Name, View.Region, View.etc, View.Data, View.Data2,
History.Date, History.Code
FROM View
INNER JOIN History ON
History.ID = View.ID
WHERE
View.Region = 58 AND
History.Date = '11/28/2006'
UNION
SELECT View.ID, View.Name, View.Region, View.etc, View.Data, View.Data2,
History.Date, History.Code
FROM View
INNER JOIN History ON
History.ID = View.ID
WHERE
View.Region = 58 AND
History.Code = '2007X';

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Merennulli" <maross@.sdf.lonestar.orgwrote in message
news:1170979522.693854.323490@.s48g2000cws.googlegr oups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

>>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;
>
Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58;
>
But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58
and ConcatenatedHistory like '%test%' ;
>
What puzzles me most is that it's taking longer to return nothing.
>
I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.
>
The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.
>
Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code

Quote:

Originally Posted by

>>From View inner join History on History.ID = View.ID

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';
>
I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.
>
~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.
>
If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.
>

|||On Feb 8, 7:27 pm, "Steve" <morrisz...@.hotmail.comwrote:

Quote:

Originally Posted by

On Feb 8, 4:05 pm, "Merennulli" <mar...@.sdf.lonestar.orgwrote:
>
>
>
>
>

Quote:

Originally Posted by

To start with, I'll give a simplified overview of my data.

Quote:

Originally Posted by

BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc

Quote:

Originally Posted by

OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data

Quote:

Originally Posted by

ProblemTable (4mil rows)
ID | ConcatenatedHistory

Quote:

Originally Posted by

The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'

Quote:

Originally Posted by

Using left outer joins (all from base, the rest optional) I've got a
view something like:

Quote:

Originally Posted by

Quote:

Originally Posted by

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View

Quote:

Originally Posted by

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

Quote:

Originally Posted by

Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View

Quote:

Originally Posted by

Where Region = 58;

Quote:

Originally Posted by

But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View

Quote:

Originally Posted by

Where Region = 58
and ConcatenatedHistory like '%test%' ;

Quote:

Originally Posted by

What puzzles me most is that it's taking longer to return nothing.

Quote:

Originally Posted by

I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.

Quote:

Originally Posted by

Quote:

Originally Posted by

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code>From View inner join History on History.ID = View.ID

Quote:

Originally Posted by

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

Quote:

Originally Posted by

I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.

Quote:

Originally Posted by

Quote:

Originally Posted by

If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.

>
do you query the concatenated data, "normalized", in a way that you
could use FULL TEXT ?- Hide quoted text -
>
- Show quoted text -

Steve,

The "normalized" data broke the code and date into a datetime field
and a 5 character char (eg. '2006X'). With that change, I was doing
exact matching instead of "like" comparisons. As far as my knowledge
extends, that should have more than compensated for the table size
difference (4mil vs 25mil and later vs 9mil). My first assumption was
that the indexes were wrong, but a non-clustered index on the ID and
Code fields should have been correct for this. I can try a clustered
on the pair, but I don't see where that would improve performance.|||Merennulli (maross@.sdf.lonestar.org) writes:

Quote:

Originally Posted by

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

I completely agree with Dan that normalising ConcatenatedHistory into
its own table, would give you better performance. But not know the
tables or indexes its difficult to say why your attempt failed.

The one thing I can suggest to improve the speed of the current
query is that you add a COLLATE clause to force a binary collation:

ConcatenatedHistory LIKE '%2007%' COLLATE Latin1_General_BIN

this is particular important if you use a Windows collation or your
column is varchar.

Quote:

Originally Posted by

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code
From View inner join History on History.ID = View.ID
Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

I don't really know what this code is. Isn't that just a date or
rather a period? And is that really the WHERE clause? Or should it
be:

Where View.Region = 58
(and History.Date = '11/28/2006' or History.Code = '2007X')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 10, 10:55 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Merennulli (mar...@.sdf.lonestar.org) writes:

Quote:

Originally Posted by

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

>
I completely agree with Dan that normalising ConcatenatedHistory into
its own table, would give you better performance. But not know the
tables or indexes its difficult to say why your attempt failed.

Sorry, it looks like my last message in response to Dan didn't go
through.
The problem was indeed my index. It failed because I had the ID first
and the date second.
Flipping the field order in the index brought my time down to about
5-8 seconds.

My thought had been that the query would use start with the other side
of the join - the view, narrow it down first and tie to the index
values, then find the date out of the remaining small section of the
index. Instead it seems it started with the opposite side of the join
from what I expected. Because of that it was within an average of 10
places of being enforced as completely random from the date field's
perspective.

Thanks for pointing me in the right direction.

Hi all,

Sorry for spamming you with this, I thought it might be the best way to get your opinion on the topic.

OVERVIEW:

We should not use MS internal procedures, since MS has the right to change/remove them at will. Given that, should the following code be allowed, or not. If not, how does anyone suggest, handling checking for the existance of a file that has been given as a parameter to some T-SQL.

--<this code is just an example, hence not neat etc.>

SET NOCOUNT ON

DECLARE @.FilePath varchar(1024)

SET @.FilePath = 'D:\Sample Data\Files Processing URL\PromotionRulesActive.txt'

--substitute a local file for testing locally

CREATE TABLE #FileExistance ([File Exists] smallint, [File is a Directory] smallint, [Parent Directory Exists] smallint)

insert into #FileExistance exec xp_fileexist @.FilePath

select * from #FileExistance

drop table #FileExistance

GethWho,
Why don't you check that from your calling code?
TSQL is not intended to be used for such purposes, and when a functionality as simple as checking whether a file exists or not is missing, that simply means you are not supposed to need such funtionality. I am sure Microsoft has "hidden" that SP intentionally, because when you do such things from SQL it almost certainly means that something is going wrong.

|||thanks for the response!
the reason I've been considering this is as part of design-by-contract. Basically, I have an SP that will get given a file location, and will use the path to do an insert into SQL. Making use of the file to do a bulk insert, or a OPENROWSET open of the file, is a T-SQL activity.
My intention is to ensure that before trying to execute the code, I ensure that the inputs I receive meet my contract - i.e that they identify a valid file location. That's why I've been toying with the xp_fileexist.
I suspect that at the end of the day, initially I will have to leave off doing the file existance checking, and eventually (when I have time to learn some c# and then do the coding) I will create my own up_fileexist code in an assembly, and use that.
Thanks for the interest and response - I welcome more comments/feedback/ideas|||

GethWho, thank You for your resonce too.
Anyhow I insist that checking a file existance is not a T-SQL activity. Indeed, bulk inserting is.
All I am saying that the code that calls your SP should make sure the file it passes exists. Validation such as that should not be done on behalf of data manipulation layer of the application. Even if the source that commits the file location to your SP is untrustworthy, you might consider creating some routine that checks the file location before submitting it to SP.
You should distribute the roles of your application between logical layers. Mixing the roles will make a maintainance nighmare out of nothing. A proper design can never end in a model where SQL needs to validate file locations, text inputs or other things like that. This is business layer responsibility.
Especially-code like that will take you approximately 5 minutes to write(in VB.net or VB6.0), even if you aren't familiar with the language. I can help you on behalf of that, mail me 'andranik at armsoft . am' if you find any difficulties.

|||Hi Andranik,
Thanks for confirming my suspicions. I think I'll try and get the time to write the .Net/C# - from what I've heard from you and some other sources, it may be something simple enough for even me to write :-)
Just a question regards the comment of trustworthiness of code. My impression of design-by-contract is that it is a methodology/design practice that drives one to write robust, resilient code that doesn't necessarily trust the calling code - hence the pre- and post- contract checking to ensure that components are robust enough to all truly robust applications. Surely that applies to all levels, and not just the front and middle tier's?
I'd appreciate some comments/pointers to good articles on design-by-contract (I'll be googling myself, but you/someone may already have a good link), and especially how it applies/doesn't apply to the data access layer.
Thanks again!
|||

Hi GethWho,
I'd reccommend Lhodka's book, "Expert C# Business Objects".

Indeed, you must verify the data at some points. You should check whether the data received from a client browser or a windows form is valid. Meanwhile, doing the same validation on all tiers is indeed redundant-you will end up writing the same code for UI, application and maybe even data access layer!
The right design choice is to encapsulate all object-related validation into object itself. In this case, using "mobile objects", that is, objects that are passed by value over the network, you can reuse the validation code on UI and application layers. Use of mobile objects has many other benefits and several drawbacks.
Data manipulation code is usually embedded into object code too. You should indeed separate the methods that read from/write into database from other methods. In this case, when a data manipulation code is called, you can make sure that the object to be persisted is valid. Indeed, no SQL-side validation is needed-the code calling SQL server-side code guarantees that the object data is valid and consistent. Otherwise, you will end doing business-layer oriented validation is SQL SP, which is...not the right choice :)
I think we should make the further discussion private because this is getting out of the topic of this forum. Feel free to mail me,
'andranik at armsoft . am'

Wednesday, March 28, 2012

Optimizing a big query

Friday, March 9, 2012

opinion re: xp_fileexist

OpenXML Identity Insert

Blog Archive

About Me