Doubts on Indexing.

Hi,

set @.DN=''

set @.DN=@.DN+'AA9999'

select @.ID = ID from TableA where status='A' and ColumnA like '%'+@.DN

ColumnA is NonClusteredIndex

When I use = symbol it goes for Index Seek (like below query) otherwise its going for table scan. Could anyone advice me which index need to be used for getting 'Index Seek' when I use like Operator. Basically TableA has millions of records.

select @.ID = ID from TableA where status='A' and Code = 'AA9999'

Regards

JMR

I think it is the normal behavior because SQL have to compare all ColumnA values with a pattern and have to go row to row; in the other select SQL look at an unique values.

So, it is recommended to avoid like operator in WHERE clause.

|||

If you use LIKE + '%xxxxx' , there is no option; It forced to use Scan. Bcs we don't know where start. When the pattern will match.

If you use LIKE + 'xxxx%', it might use Index Scan / Seek depend with your data density.

bcs the LIke 'xxxx%' will be converted as col >= 'xxxx' and col < 'xxxxY';

Don't know where to start

I'm still a novice in Integration Services, and i need to calculate the
time that our tickets are in a Pending Status. The problem that i'm
having is that the Pending Start time, and the Pending End time, are
not in the same record. The table that need to query is like the
following
Ticket__ DateTime Summary
cs00001 1/1/2006 13:00 Ticket entered Pending status
cs00002 1/1/2006 13:03 Some other ticket activity
cs00001 1/1/2006 13:20 Some other activity
cs00001 1/1/2006 13:30 Ticket exited Pending status
I think i need to do this as a SSIS job as we already have one that
imports all the data from our oracle database to SQL (for reporting),
and i would like to run this as a 2nd step in that existing job - but i
just have no idea where to start.
Any help would be appreciated.
Thank you.select ticketId, datediff(min, StartTime, EndTime )
from (
select t1.ticketId, StartTime = t1.DateTime, t2.StartTime as EndTime
from TicketTable t1
INNER JOIN TicketTable t2
ON t1.ticketId = t2.ticketID
and t1.summary ='Ticket entered Pending status'
and t2.summary = 'Ticket exited Pending status'
) as tmp1
You need to get the start and the stop on the same row, then do the datediff
on the 2 dates. Basically, self-join the table by ticketid and whatever you
need to determine the start time and the end time.
-Tim
"Brent" <Brent.Maloney@.intecbilling.com> wrote in message
news:1150839402.988411.64780@.b68g2000cwa.googlegroups.com...
> I'm still a novice in Integration Services, and i need to calculate the
> time that our tickets are in a Pending Status. The problem that i'm
> having is that the Pending Start time, and the Pending End time, are
> not in the same record. The table that need to query is like the
> following
> Ticket__ DateTime Summary
> cs00001 1/1/2006 13:00 Ticket entered Pending status
> cs00002 1/1/2006 13:03 Some other ticket activity
> cs00001 1/1/2006 13:20 Some other activity
> cs00001 1/1/2006 13:30 Ticket exited Pending status
> I think i need to do this as a SSIS job as we already have one that
> imports all the data from our oracle database to SQL (for reporting),
> and i would like to run this as a 2nd step in that existing job - but i
> just have no idea where to start.
> Any help would be appreciated.
> Thank you.
>|||>> I need to calculate the time that our tickets are in a Pending Status. The pr
oblem that I'm having is that the Pending Start time, and the Pending End time, a
re not in the same record [sic] <<
Your error is bedrock, fundamental and deadly. Rows are not records.
Nothing alike. NOTHING. NADA!! A record is a unit of contigous
physical storage in a file system. A row (no matter how it is
physically implemented; which does not have to be contigous storage) is
a single **fact** about a member of a set.
What is the fact in your example; the whole fact and nothing but the
fact' The fact is that "ticket # XXXX was pending from start time
tttt until finish time ffff" with the constraints that (start time <
finish time), etc. from the DDL (aka "Universe of Discourse").
You mimicked a paper sign-in-sign-out sheet and not a whole fact. Let
(finish-time IS NULL) mean that the process is on-going and not yet
complete.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
if you had proper netiquette, you might have posted (with the ISO-8601
temporal formats, please!!)
CREATE TABLE TicketHistory
(ticket_nbr CHAR (7) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME, -- null is current
status_code CHAR(3) DEFAULT 'new' NOT NULL
CHECK (status_code IN (..) ),
PRIMARY KEY ()ticket_nbr, start_date));

Doing the same replace on several columns?

In the derived column task you can choose each column and write an expression for each column. But when you need to do a <ISNULL(status) ? "0" : statusdato> on 40-50 columns it get kind of irritating. Is there a way easy to do the sam expression on a selection of columns like a sort of derived column task, where you write an expression and assign that to a selection of columns (otherwise this would be a wish :-) )

Sorry, there is no such feature. Sounds like a good candidate to enter as a suggestion.

Note: it would be possible to write code that programmatically does what you are looking for, by loading a package, finding the derived column transform, getting the input column collection, and setting the expression properties.

Thanks
Mark

|||Ascential's (now IBM) DataStage had a really nice interfaces for doing just what you've asked. while SSIS doesn't have this interface, it does have the Script Component, which I find much more flexible than DataStage's Transformer stage.

I had a similar dilemma with having a good number of my input fields requiring trimming. Following the lead of several custom component examples I got rid of the ProcessInput_Row (I think that's what's in there) and overrode PreExecute and ProcessInput with the following in a Script Componet, of type Transform.

The biggest drawback is having to select all the checkboxes on the Input Columns tab and set them all to ReadWrite, but with some keyboard skills, that can go pretty quickly.

I've not been a programmer in any of my previous lives, so please forgive the poor form:

Public Class ScriptMain
Inherits UserComponent

Private _inputColumnInfos As ColumnInfo()

Public Structure ColumnInfo
Public bufferColumnIndex As Integer
Public lineageID As Integer
End Structure

Public Overrides Sub PreExecute()
Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)

ReDim _inputColumnInfos(input.InputColumnCollection.Count - 1)

For x As Integer = 0 To (input.InputColumnCollection.Count - 1)
Dim column As IDTSInputColumn90 = input.InputColumnCollection(x)
_inputColumnInfos(x) = New ColumnInfo()
_inputColumnInfos(x).bufferColumnIndex = input.InputColumnCollection.FindObjectIndexByID(column.ID)
_inputColumnInfos(x).lineageID = column.LineageID
Next
MyBase.PreExecute()
End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
Dim columnInfo As ColumnInfo

While (Buffer.NextRow())

For x As Integer = 0 To (_inputColumnInfos.Length - 1)
columnInfo = _inputColumnInfos(x)

Dim trxVal As String = Buffer.GetString(columnInfo.bufferColumnIndex).ToString().Trim

Buffer.SetString(columnInfo.bufferColumnIndex, trxVal)
Next
End While
End Sub

End Class