Indexing Results of Stored Proc (or new table created by one)

Hi,

I am using data from multiple databases and/or queries. It would greatly
simplify and speed things up if I could use CONTAINS in processing the
results. However, "CONTAINS" requires the data to be indexed. Due to the
amount of processing, I think it would be faster even if I had to re-index
every time.

For example, I would like to do something like this (simplified to
illustrate the desired functionality... This should show all of the words
from one table that are not contained in their current or inflectional forms
within another table):

SELECT W1.Content
FROM
(SELECT Word AS Content
FROM MyTable) W1
LEFT OUTER JOIN
(SELECT Phrase AS Content
FROM MyOtherTable) W2
ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
WHERE W2.Content IS NULL

Can the results of a procedure be indexed? If not, can I drop the results
into a new table and trigger an automatic index of it, pausing the procedure
until the indexing is done?

Or, it there another way?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simon|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simonsql

Indexing Advice

Hi guys,
I've been playing with this query for about a week now, and I still
can't find ways to speed it up. It runs for about 5 minutes. The only
table (among the 41) with significant amount of data is dbo.S_EVT_ACT,
which contains about 900,000 records. The query is automatically
generated by Siebel, and therefore not really modifiable. And hints
are not really possible. While looking at the exec plan, we can see
that what takes so long is a clustered index scan of T1. This
clustered index is defined on a column ROW_ID (a Siebel-generated
primary key used for every table). I tried to define all kinds of
indexes in order to bypass this clustered index scan, but no success
so far ... Here's the query:

SELECT ...
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T2 ON T1.ROW_ID =
T2.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_FN_APPR T3 ON T2.AMS_ACT_ID =
T3.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.PERFRM_BY_OU_ID =
T4.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.PRI_LST_ID =
T5.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T6 ON T1.PR_CON_ID =
T6.PAR_ROW_ID
AND T1.PR_CON_ID = T6.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T7 ON T1.RATE_LST_ID =
T7.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T8 ON T1.ROW_ID =
T8.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ASSET T9 ON T1.ASSET_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T10 ON T1.PR_CON_ID =
T10.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_DOC_AGREE T11 ON T1.AGREEMENT_ID =
T11.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T12 ON T1.PR_EXP_RPT_ID =
T12.ROW_ID
LEFT OUTER JOIN dbo.S_INS_CLAIM T13 ON T1.INSCLM_ID =
T13.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT_SS T14 ON T1.ROW_ID =
T14.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_INT_INSTANCE T15 ON T14.OWN_INST_ID =
T15.ROW_ID
LEFT OUTER JOIN dbo.S_ME_EVT_LS T16 ON T1.ME_EVT_ID =
T16.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T17 ON T1.OPTY_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T18 ON T1.TARGET_OU_ID =
T18.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T19 ON T1.PART_RPR_ID =
T19.ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T20 ON T1.OWNER_POSTN_ID =
T20.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T21 ON T1.SRA_DEFECT_ID =
T21.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T22 ON T1.PROJ_ID = T22.ROW_ID
LEFT OUTER JOIN dbo.S_PROJITEM T23 ON T1.PROJ_ITEM_ID =
T23.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T24 ON T1.SRC_ID = T24.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T25 ON T1.SRA_SR_ID =
T25.ROW_ID
LEFT OUTER JOIN dbo.S_TMPL_PLANITEM T26 ON T1.ASSESS_TMPL_ID
=
T26.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T27 ON T1.PR_TMSHT_LINE_ID
=
T27.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT T28 ON T1.TEMPLATE_ID =
T28.ROW_ID
LEFT OUTER JOIN dbo.S_SUSP_ACT T29 ON T1.ROW_ID =
T29.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_EVT_MAIL T30 ON T1.ROW_ID =
T30.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_SRV_ACT T31 ON T1.ROW_ID =
T31.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ACT_EMP T32 ON T1.OWNER_PER_ID =
T32.EMP_ID AND
T1.ROW_ID = T32.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_PARTY T33 ON T32.EMP_ID = T33.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_FNX T34 ON T32.EMP_ID =
T34.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_USER T35 ON T32.EMP_ID =
T35.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PARTY T36 ON T1.PR_CON_ID = T36.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T37 ON T1.PR_CON_ID =
T37.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_FNX T38 ON T1.PR_CON_ID =
T38.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T39 ON T1.PR_PRDINT_ID =
T39.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PROD_APPL T40 ON T1.PR_PRDINT_ID =
T40.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T41 ON T40.PRDINT_ID =
T41.ROW_ID
WHERE
(
(T1.PAR_EVT_ID = T1.ROW_ID OR T1.PAR_EVT_ID IS NULL OR
T1.PAR_EVT_ID !=
T1.ROW_ID AND T1.OPTY_ID IS NOT NULL OR T1.SUBTYPE_CD =
'General' AND
T1.TODO_CD != 'Marketing eEvent') AND
(T1.APPT_REPT_REPL_CD IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' AND
(T1.OPTY_ID IS NULL OR T17.SECURE_FLG = 'N' OR T1.OPTY_ID IN
(
SELECT SQ1_T2.OPTY_ID
FROM dbo.S_PARTY SQ1_T1
INNER JOIN dbo.S_OPTY_POSTN SQ1_T2
ON SQ1_T2.POSITION_ID = SQ1_T1.ROW_ID
INNER JOIN dbo.S_POSTN SQ1_T3
ON SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT SQ1_T4
ON SQ1_T3.PR_EMP_ID = SQ1_T4.PAR_ROW_ID
WHERE
(SQ1_T4.ROW_ID = '1-23NDP')
)
)
)
AND
(T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
'1-23NDP')) AND
(T1.APPT_START_DT > DATEADD
(SECOND, ROUND (-183 * 86400, 0), '01/15/2004 00:00:00')
OR
T1.TODO_ACTL_END_DT >
DATEADD (SECOND, ROUND (-183 * 86400, 0), '01/15/2004
00:00:00'))

Anyone's got an idea of which index on T1 might help me?

Daniel"Daniel Roy" <danielroy10junk@.hotmail.com> wrote in message
news:3722db.0401190652.41d33a30@.posting.google.com ...
> Hi guys,
> I've been playing with this query for about a week now, and I still
> can't find ways to speed it up. It runs for about 5 minutes. The only
> table (among the 41) with significant amount of data is dbo.S_EVT_ACT,
> which contains about 900,000 records. The query is automatically
> generated by Siebel, and therefore not really modifiable. And hints
> are not really possible. While looking at the exec plan, we can see
> that what takes so long is a clustered index scan of T1. This
> clustered index is defined on a column ROW_ID (a Siebel-generated
> primary key used for every table). I tried to define all kinds of
> indexes in order to bypass this clustered index scan, but no success
> so far ... Here's the query:
> SELECT ...
> FROM
> dbo.S_EVT_ACT T1
> LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T2 ON T1.ROW_ID =
> T2.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_FN_APPR T3 ON T2.AMS_ACT_ID =
> T3.ROW_ID
> LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.PERFRM_BY_OU_ID =
> T4.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.PRI_LST_ID =
> T5.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT T6 ON T1.PR_CON_ID =
> T6.PAR_ROW_ID
> AND T1.PR_CON_ID = T6.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PRI_LST T7 ON T1.RATE_LST_ID =
> T7.ROW_ID
> LEFT OUTER JOIN dbo.S_ACT_PRDINT T8 ON T1.ROW_ID =
> T8.ACTIVITY_ID
> LEFT OUTER JOIN dbo.S_ASSET T9 ON T1.ASSET_ID = T9.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT T10 ON T1.PR_CON_ID =
> T10.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_DOC_AGREE T11 ON T1.AGREEMENT_ID =
> T11.ROW_ID
> LEFT OUTER JOIN dbo.S_EXP_RPT T12 ON T1.PR_EXP_RPT_ID =
> T12.ROW_ID
> LEFT OUTER JOIN dbo.S_INS_CLAIM T13 ON T1.INSCLM_ID =
> T13.ROW_ID
> LEFT OUTER JOIN dbo.S_EVT_ACT_SS T14 ON T1.ROW_ID =
> T14.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_INT_INSTANCE T15 ON T14.OWN_INST_ID =
> T15.ROW_ID
> LEFT OUTER JOIN dbo.S_ME_EVT_LS T16 ON T1.ME_EVT_ID =
> T16.ROW_ID
> LEFT OUTER JOIN dbo.S_OPTY T17 ON T1.OPTY_ID = T17.ROW_ID
> LEFT OUTER JOIN dbo.S_ORG_EXT T18 ON T1.TARGET_OU_ID =
> T18.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PART_RPR T19 ON T1.PART_RPR_ID =
> T19.ROW_ID
> LEFT OUTER JOIN dbo.S_POSTN T20 ON T1.OWNER_POSTN_ID =
> T20.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PROD_DEFECT T21 ON T1.SRA_DEFECT_ID =
> T21.ROW_ID
> LEFT OUTER JOIN dbo.S_PROJ T22 ON T1.PROJ_ID = T22.ROW_ID
> LEFT OUTER JOIN dbo.S_PROJITEM T23 ON T1.PROJ_ITEM_ID =
> T23.ROW_ID
> LEFT OUTER JOIN dbo.S_SRC T24 ON T1.SRC_ID = T24.ROW_ID
> LEFT OUTER JOIN dbo.S_SRV_REQ T25 ON T1.SRA_SR_ID =
> T25.ROW_ID
> LEFT OUTER JOIN dbo.S_TMPL_PLANITEM T26 ON T1.ASSESS_TMPL_ID
> =
> T26.ROW_ID
> LEFT OUTER JOIN dbo.S_TMSHT_LINE T27 ON T1.PR_TMSHT_LINE_ID
> =
> T27.ROW_ID
> LEFT OUTER JOIN dbo.S_EVT_ACT T28 ON T1.TEMPLATE_ID =
> T28.ROW_ID
> LEFT OUTER JOIN dbo.S_SUSP_ACT T29 ON T1.ROW_ID =
> T29.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_EVT_MAIL T30 ON T1.ROW_ID =
> T30.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_SRV_ACT T31 ON T1.ROW_ID =
> T31.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_ACT_EMP T32 ON T1.OWNER_PER_ID =
> T32.EMP_ID AND
> T1.ROW_ID = T32.ACTIVITY_ID
> LEFT OUTER JOIN dbo.S_PARTY T33 ON T32.EMP_ID = T33.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT_FNX T34 ON T32.EMP_ID =
> T34.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_USER T35 ON T32.EMP_ID =
> T35.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PARTY T36 ON T1.PR_CON_ID = T36.ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT T37 ON T1.PR_CON_ID =
> T37.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT_FNX T38 ON T1.PR_CON_ID =
> T38.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_PROD_INT T39 ON T1.PR_PRDINT_ID =
> T39.ROW_ID
> LEFT OUTER JOIN dbo.S_ACT_PROD_APPL T40 ON T1.PR_PRDINT_ID =
> T40.ROW_ID
> LEFT OUTER JOIN dbo.S_PROD_INT T41 ON T40.PRDINT_ID =
> T41.ROW_ID
> WHERE
> (
> (T1.PAR_EVT_ID = T1.ROW_ID OR T1.PAR_EVT_ID IS NULL OR
> T1.PAR_EVT_ID !=
> T1.ROW_ID AND T1.OPTY_ID IS NOT NULL OR T1.SUBTYPE_CD =
> 'General' AND
> T1.TODO_CD != 'Marketing eEvent') AND
> (T1.APPT_REPT_REPL_CD IS NULL) AND
> (T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' AND
> (T1.OPTY_ID IS NULL OR T17.SECURE_FLG = 'N' OR T1.OPTY_ID IN
> (
> SELECT SQ1_T2.OPTY_ID
> FROM dbo.S_PARTY SQ1_T1
> INNER JOIN dbo.S_OPTY_POSTN SQ1_T2
> ON SQ1_T2.POSITION_ID = SQ1_T1.ROW_ID
> INNER JOIN dbo.S_POSTN SQ1_T3
> ON SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID
> LEFT OUTER JOIN dbo.S_CONTACT SQ1_T4
> ON SQ1_T3.PR_EMP_ID = SQ1_T4.PAR_ROW_ID
> WHERE
> (SQ1_T4.ROW_ID = '1-23NDP')
> )
> )
> )
> AND
> (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
> '1-23NDP')) AND
> (T1.APPT_START_DT > DATEADD
> (SECOND, ROUND (-183 * 86400, 0), '01/15/2004 00:00:00')
> OR
> T1.TODO_ACTL_END_DT >
> DATEADD (SECOND, ROUND (-183 * 86400, 0), '01/15/2004
> 00:00:00'))
> Anyone's got an idea of which index on T1 might help me?
> Daniel

According to your query, it looks like you will always have either an index
or table scan of dbo.S_EVT_ACT - it is on the left side of a LEFT JOIN, and
that means that all rows from the table will always be returned. The rows
may be filtered further by the WHERE clause, but MSSQL will require all the
rows as part of the join.

If you had control over the query, you might be able to re-write it with the
filter conditions in the join, not the WHERE clause, or possibly change the
join order to put a smaller table first (although both of those would change
the logic of your query, so might not be straightforward). You could
investigate if Siebel has some facility for changing the join order of a
query, if you can find another join order which is logically equivalent.

Other than that, there probably isn't much you can do, if you don't control
the query itself. One thing that might be possible would be to create
indexed views of common joins, then use the views instead of tables in your
queries. But there are lots of conditions attached to creating indexed views
(including no outer joins), so you would need to do some research and
testing.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> According to your query, it looks like you will always have either an
> index or table scan of dbo.S_EVT_ACT - it is on the left side of a LEFT
> JOIN, and that means that all rows from the table will always be
> returned. The rows may be filtered further by the WHERE clause, but
> MSSQL will require all the rows as part of the join.

I agree that the query is likely to be a hopeless case, but I don't
think you are right on your assement of the left join. Of course SQL Server
can apply the WHERE conditions on S_EVT_ACT, before it does all the
left joins. Consider for instance this query:

select *
from Customers C
left join Orders O ON C.CustomerID = O.CustomerID
where C.City = 'Berlin'

This query performs an Index Seek on C.City.

The problem with Daniel's query is that the WHERE conditions are very
complex. The simplest condition is that T1.APPT_REPT_REPL_CD must be
NULL. And if this condition filters most of the rows, we have an index
candidate here. I'm not holding my breath, though.

Other possibilities would be to create some combined index on some
combinations of columns, or create separate indexes and hope for
index intersection. But it definitely looks like an uphil battle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Simon for your response. Isn't there any way that the
optimizer can decide first to filter the rows from S_EVT_ACT (with a
non-clustered index and a clustered index lookup), and only afterwards
use the resulting rows to address the remaining 40 tables?

Daniel|||Daniel Roy (danielroy10junk@.hotmail.com) writes:
> Thank you Simon for your response. Isn't there any way that the
> optimizer can decide first to filter the rows from S_EVT_ACT (with a
> non-clustered index and a clustered index lookup), and only afterwards
> use the resulting rows to address the remaining 40 tables?

As you might have seen from my post, there is. And in fact it is
likely that what SQL Server will do. However, the conditions are
so complex, that most likely it will have to scan the table anyway.

One of the left joined-tables are special, and that is T17 which
appears in the WHERE clause, so it is possible this has to be
examined first.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Daniel Roy (danielroy10junk@.hotmail.com) writes:
> > Thank you Simon for your response. Isn't there any way that the
> > optimizer can decide first to filter the rows from S_EVT_ACT (with a
> > non-clustered index and a clustered index lookup), and only afterwards
> > use the resulting rows to address the remaining 40 tables?
> As you might have seen from my post, there is. And in fact it is
> likely that what SQL Server will do. However, the conditions are
> so complex, that most likely it will have to scan the table anyway.
> One of the left joined-tables are special, and that is T17 which
> appears in the WHERE clause, so it is possible this has to be
> examined first.

Thanx Erland and Simon for your kind advice. Now that we know that a
(non-clustered) index could possibly be used first before dealing with
the joins, anyone could tell me which index to T1 should be created,
according to the WHERE clause? I agree with you, Erland, when you say
it will be an uphill battle, but I think it's worth a try. Here are
some specific questions which stop me currently:
- can a comparison between 2 columns of the same table be resolved
with a non-clustered index (T1.PAR_EVT_ID = T1.ROW_ID)?
- can an IS NULL condition resolved with a non-clustered index
(T1.PAR_EVT_ID IS NULL)?
- can an inequality between 2 columns of the same table be resolved
with a non-clustered index (T1.PAR_EVT_ID != T1.ROW_ID)?
- can an IS NOT NULL condition resolved with a non-clustered index
(T1.OPTY_ID IS NOT NULL)?
...

As you can see, I'm just looking for guidance on the way that the SQL
Server's optimizer can make use of non-clustered indexes, in order to
help my chances of creating one which would make the clustered index
scan not necessary.

Daniel|||Daniel Roy (danielroy10junk@.hotmail.com) writes:
> Thanx Erland and Simon for your kind advice. Now that we know that a
> (non-clustered) index could possibly be used first before dealing with
> the joins, anyone could tell me which index to T1 should be created,
> according to the WHERE clause? I agree with you, Erland, when you say
> it will be an uphill battle, but I think it's worth a try. Here are
> some specific questions which stop me currently:
> - can a comparison between 2 columns of the same table be resolved
> with a non-clustered index (T1.PAR_EVT_ID = T1.ROW_ID)?
> - can an IS NULL condition resolved with a non-clustered index
> (T1.PAR_EVT_ID IS NULL)?
> - can an inequality between 2 columns of the same table be resolved
> with a non-clustered index (T1.PAR_EVT_ID != T1.ROW_ID)?
> - can an IS NOT NULL condition resolved with a non-clustered index
> (T1.OPTY_ID IS NOT NULL)?
> ...

All sorts of conditions can be resolved by the index, this is not the
problem. The problem is whether the index can be selective enough or not.

Consider these two queries:

select * from Northwind..Orders WHERE CustomerID like 'N%'
select * from Northwind..Orders WHERE CustomerID like 'A%'

If you run them from Query Analyzer after having pressed CTRL-K
to get a tab with the execution plan, you will find that the
first query uses the non-clustered index on CustomerID, while
the second performs a clustered index scan.

Why is this? When SQL Server uses a non-clustered index to retrieve
data, and the query includes columns not present in the index (or
in the clustered index, of which the keys constitute the row pointers
of the non-clustered index) SQL Server must access the data pages.
This is known as "bookmark lookup". That is one access to a page
for each row, so the more rows you find this way, the more likely
you will access the same page more than once. Thus, at some level,
a table scan is cheaper. In the example above there are very few
orders from Customers whose IDs start with N, but there are a bunch
for A.

Now consider this query:

select *
from Northwind..Customers
WHERE CompanyName LIKE 'N%'
OR City = 'Berlin'

There is one index on CompanyName and one City, and if you run
the query in QA, you will find that SQL Server uses both indexes.
Again, if you change 'N%' to 'A%', SQL Server instead opts for a
table scan.

Another important thing to consider is the order of the columns
in a multi-columns index. Say that you have a non-clustered index
on the columns (a, b), and you issue the query:

SELECT * FROM tbl WHERE b = 3

Will SQL Server use the index? Maybe. But it would have to scan the
entire index, since the entries with b = 3 are scattered all over
the index. But if SQL Server has statistics that gives an estimate
that this query will only hit 3 of a million rows, SQL Server will
scan the non-clustered index, since scanning the index require
few page reads than scanning the data pages. (Assuming that the
table has a coupld of more columns.)

In these examples, I have used examples of equality. Of course
conditions with != can also be used, but as you may have realized
by now, they are not very likely to be selective enough.

Note that I say selective, this is not a question about actual
facts, but estimates drawn from the statistics SQL Server have
availble. In these examples, input value has been constants.
When the input value comes from a variable or another column,
SQL Server still can use the distribution, but the plan will be
the same no matter the input value, beause the optimizer does not
that value when it builds the plan.

This should give you some idea. It is possible that you can create
one or more non-clustered indexes, to resolve this, but I would
not hold high hopes.

Maybe I should add one more thing. You could lump about all
columns that appear in the WHERE clause in no particular order.
If there are only one or two more small columns in the table, this
is not likely to have any effect. But there are lot of more columns,
or the remaining columns are big, then you can make some gains
this way. Then SQL Server can evaluate the WHERE clause by scanning
the index, and as I noted above, this is cheaper than scanning
the entire table. (Because there are fewer pages to read.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Daniel Roy (danielroy10junk@.hotmail.com) writes:
> As you can see, I'm just looking for guidance on the way that the SQL
> Server's optimizer can make use of non-clustered indexes, in order to
> help my chances of creating one which would make the clustered index
> scan not necessary.

And, oh, there is one more thing you definitely should try if you have
not already, and that is DBCC DBREINDEX. It is not going to remove
the clustered index scan, but if the table is heavily fragmented, reindexing
can make that scan a lot cheaper.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Indexing

I have a table with 5 million rows. This table has 25 columns. I am writing
a view which use 7 of its columns in JOINs with other tables. To speed up
this view I am thinking about creating index(es). Should I create one index
which has those 7 columns or 7 indexes for each column? Do my joins should
follow the same order as the columns in the index?
Thanks"JY" <jy1970us@.yahoo.com> wrote in message
news:hOBnf.1019$El.172939@.news20.bellglobal.com...
>I have a table with 5 million rows. This table has 25 columns. I am writing
>a view which use 7 of its columns in JOINs with other tables. To speed up
>this view I am thinking about creating index(es). Should I create one index
>which has those 7 columns or 7 indexes for each column? Do my joins should
>follow the same order as the columns in the index?
> Thanks
>
Read up on how indexing works.
There are a lot of questions regarding their use.
For your specific example, I am making the following assumptions.
1. You don't do massive updates/inserts to this 5 million row table.
2. You are not doing any other queries on this table other than your view.
With that in mind, try it both ways and look at the execution plans.
A couple of notes:
When creating the index using all 7 columns in the join. The column index
order should be from most selective to least selective. (i.e. The first
column should have the most unique values in it, the second column should
have the next highest number of unique values in it. The last column should
have the fewest unique values in it).
As far as your join order, don't worry about it. SQLs Query Optimizer
should be able to figure it out.
Good luck.
Rick Sawtell
MCT, MCSD, MCDBA|||
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:O$SPOrAAGHA.208@.tk2msftngp13.phx.gbl...
> "JY" <jy1970us@.yahoo.com> wrote in message
> news:hOBnf.1019$El.172939@.news20.bellglobal.com...
> Read up on how indexing works.
> There are a lot of questions regarding their use.
> For your specific example, I am making the following assumptions.
> 1. You don't do massive updates/inserts to this 5 million row table.
> 2. You are not doing any other queries on this table other than your
> view.
> With that in mind, try it both ways and look at the execution plans.
> A couple of notes:
> When creating the index using all 7 columns in the join. The column index
> order should be from most selective to least selective. (i.e. The first
> column should have the most unique values in it, the second column should
> have the next highest number of unique values in it. The last column
> should have the fewest unique values in it).
> As far as your join order, don't worry about it. SQLs Query Optimizer
> should be able to figure it out.
>
>
> Good luck.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
Hi Rick,
Thanks for your suggestion. My query is taking 40.73 minutes to complete.
I created an index on those 7 columns with the column index order from most
selective to least selective. Now the query is taking 33.41 minutes which is
good but it is still taking a lot of time.
I run Index Tuning Wizard for my query and it suggests an index which
suggests an index. Creating it and using it, my query takes 37.89 minutes.
Can you suggest anything else?
Thanks|||when you say the joins use 7 of the columns do you mean
1) it joins on all 7 columns to each other table
(table1.col1 = table2.col1 and ... and table1.col7 = table2.col7)
or
2) or that only 7 of the columns are used in any of the joins
(join 1: table1.col1 = table2.col1, join 2: table1.col1 = table3.col1
and table1.col3 = table3.col3, etc.)
?
only if you will join on all 7 columns in at least one join, should you
create an index on all 7 columns [even then, consider the selectivity]
otherwise, examine your joins and choose the most selective ones for the
indexes. also, one index may be good for more than one set of joins -
e.g., join 1 is on col1 and col2, join 2 is on col1, col2 and col3 -- a
single index on col1, col2 and col3 will cover both (if col1 and col2
are the first 2 columns in the index...)
also, make sure the tables you are joining to are indexed properly to
match - otherwise performance won't increase all that much
[posting DDL for the tables (including existing keys and indexes) and
views would help]
JY wrote:
> I have a table with 5 million rows. This table has 25 columns. I am writin
g
> a view which use 7 of its columns in JOINs with other tables. To speed up
> this view I am thinking about creating index(es). Should I create one inde
x
> which has those 7 columns or 7 indexes for each column? Do my joins should
> follow the same order as the columns in the index?
> Thanks
>|||> Hi Rick,
> Thanks for your suggestion. My query is taking 40.73 minutes to complete.
> I created an index on those 7 columns with the column index order from
> most selective to least selective. Now the query is taking 33.41 minutes
> which is good but it is still taking a lot of time.
> I run Index Tuning Wizard for my query and it suggests an index which
> suggests an index. Creating it and using it, my query takes 37.89 minutes.
> Can you suggest anything else?
> Thanks
>
>
Are there indexes on the JOIN columns in the other tables that you are
joining to?
Take a look at the Estimated Execution Plan for your query and see what it
shows.
If you have TABLE SCANS, these columns are good candidates for an index.
It's hard to say without seeing your table structure, indexes and the query
itself.
Rick|||"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:umXm37BAGHA.3372@.TK2MSFTNGP12.phx.gbl...
> Are there indexes on the JOIN columns in the other tables that you are
> joining to?
> Take a look at the Estimated Execution Plan for your query and see what it
> shows.
> If you have TABLE SCANS, these columns are good candidates for an index.
> It's hard to say without seeing your table structure, indexes and the
> query itself.
> Rick
>
ok Rick here is the information:
EXEC sp_addtype N'T_CODE', N'int', N'null'
GO
EXEC sp_addtype N'T_VALUE', N'int', N'null'
GO
EXEC sp_addtype N'T_IDENTIFIER', N'numeric(18,0)', N'not null'
GO
EXEC sp_addtype N'T_AMOUNT', N'decimal(10,2)', N'null'
GO
EXEC sp_addtype N'T_AMOUNT4', N'decimal(12,4)', N'null'
GO
EXEC sp_addtype N'T_LONG_NAME', N'varchar (50)', N'null'
GO
CREATE TABLE [ESTUB] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PERIOD_NUMBER] [T_VALUE] NULL ,
[PAYGROUP] [T_CODE] NULL ,
[EMPLOYEE_ID] [T_IDENTIFIER] NULL ,
[LOCATION_CODE] [T_CODE] NULL ,
[DEPARTMENT_CODE] [T_CODE] NULL ,
[CHEQUE_TYPE] [T_CODE] NULL ,
[SEQUENCE] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[CODE_TYPE] [T_CODE] NULL ,
[PAY_CODE] [T_CODE] NULL ,
[CURR_HOURS] [T_AMOUNT] NULL ,
[RATE] [T_AMOUNT4] NULL ,
[CURR_AMT] [T_AMOUNT] NULL ,
[MTD_HOURS] [T_AMOUNT] NULL ,
[MTD_AMT] [T_AMOUNT] NULL ,
[YTD_HOURS] [T_AMOUNT] NULL ,
[YTD_AMT] [T_AMOUNT] NULL ,
[ACCOUNT_NUMBER] [T_LONG_NAME] NULL ,
[SORT_ORDER] [T_VALUE] NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[DISP_STATUS] [T_CODE] NULL ,
[TRANS_CODE] [T_CODE] NULL ,
CONSTRAINT [PK_ESTUB_BCK] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_ESTUB] ON [dbo].[ESTUB]([EMPLOYEE_ID], [PAY_CODE],
[PERIOD_NUMBER], [PAYGROUP], [PAYFREQCODE], [CHEQUE_TYPE]) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_GROUPING] (
[PAYGROUP] [smallint] NOT NULL ,
[COMPANY_ID] [numeric](18, 0) NOT NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PAYFREQCODE] [smallint] NOT NULL ,
CONSTRAINT [PK_PAYROLL_GROUPING] PRIMARY KEY NONCLUSTERED
(
[PAYGROUP]
) WITH FILLFACTOR = 100 ON [PRIMARY] ,
CONSTRAINT [IX_PAYROLL_GROUPING] UNIQUE NONCLUSTERED
(
[COMPANY_ID],
[PAYROLL_NUMBER],
[PAYFREQCODE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_PAYGROUP] ON
[dbo].[PAYROLL_GROUPING]([PAYGROUP]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE TABLE [EMPLOYEE_EXT] (
[EMPLOYEE_ID] [T_IDENTIFIER] NOT NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[JOB] [T_CODE] NULL ,
[ROE_REASON] [T_CODE] NULL ,
[ROE_STATUS] [T_CODE] NULL ,
[WCBCODE] [T_CODE] NULL ,
[SPLITCODE] [T_CODE] NULL ,
[COMMISSION] [T_CODE] NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[EMP_EXTCODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYGROUP] [T_CODE] NULL ,
[DELETED_FLAG] [int] NULL CONSTRAINT [DF_EMPLOYEE_EXT_DELETED_FLAG] DEFAULT
(0),
CONSTRAINT [PK_EMPLOYEE_EXT] PRIMARY KEY NONCLUSTERED
(
[EMPLOYEE_ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SYSTEM_CODE_TABLE] (
[CODE_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODE_VALUE] [int] NOT NULL ,
[LANG_TYPE] [int] NOT NULL ,
[SHORT_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTIVE] [bit] NOT NULL ,
[SORT] [int] NULL ,
[REFERENCE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SYSTEM_CODE_TABLE] PRIMARY KEY NONCLUSTERED
(
[CODE_TYPE],
[CODE_VALUE],
[LANG_TYPE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_PERIOD_EXT] (
[PERIOD_NUMBER] [int] NOT NULL ,
[paygroup_code] [int] NULL ,
[PAYFREQCODE] [int] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[EXT_PERIOD] [smallint] NULL ,
[PAY_CYCLE] [smallint] NULL ,
[DEDUCTION_CYCLE] [smallint] NULL ,
[CHEQUE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
CONSTRAINT [PK_PAYROLL_PERIOD_EXT] PRIMARY KEY CLUSTERED
(
[PERIOD_NUMBER]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAY_REQUEST] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PAYGROUP_CODE] [smallint] NULL ,
[COMPANY_ID] [numeric](18, 0) NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_NUMBER] [int] NULL ,
[REPORT_GENERATE] [numeric](1, 0) NULL ,
[PURGE_FISCALMEMOS] [numeric](1, 0) NULL ,
[RUN_TYPE] [numeric](1, 0) NULL ,
[PRINT_TIMECARDS] [numeric](1, 0) NULL ,
[SPECIAL_REPORTS] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CONSOLIDATION_REPORT] [numeric](1, 0) NULL ,
[USER_FIELD] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FREQUENCY_CODE] [int] NULL ,
[PAY_DATE] [datetime] NULL ,
[NEWPERIOD_CLEAR] [numeric](1, 0) NULL ,
[PAY_CYCLE] [numeric](1, 0) NULL ,
[DEDUCTION_CYCLE] [numeric](1, 0) NULL ,
[GENERATE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
[YEAR_END] [int] NULL ,
CONSTRAINT [PK_PAY_REQUEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_PAY_REQUEST] ON [dbo].[PAY_REQUEST]([PERIOD_NUMBER],
[RUN_TYPE]) ON [PRIMARY]
GO
And the Query is
SELECT
CAST(pg.PAYROLL_NUMBER AS varchar(10)) + '$' + ee.EMP_EXTCODE AS
EmployeeKey,
CAST(pg.PAYROLL_NUMBER AS varchar(10)) AS CompanyId,
CAST(ee.EMP_EXTCODE AS varchar(10)) AS EmployeeId,
ctpf.DESCRIPTION AS Frequency,
YEAR(ppe.cheque_date) as Year,
MONTH(ppe.cheque_date) as Month,
CAST(ppe.EXT_PERIOD AS varchar(10)) AS Period_Number,
CONVERT(varchar(10),ppe.start_date,101) AS Start_Date,
CONVERT(varchar(10),ppe.end_date,101) AS End_Date,
CONVERT(varchar(10),ppe.cheque_date,101) AS Cheque_Date,
ctct.DESCRIPTION AS Cheque_Type,
ctrt.Description AS Run_Type,
CAST(ISNULL(es.sequence,'') AS varchar(10)) AS Sequence,
ctp.EXT_CODE AS Hed,
ctp.DESCRIPTION AS Hed_Description,
isNull(es.curr_hours, 00.00) AS Curr_Hours,
es.rate AS Rate,
isNull(es.curr_amt, 00.00) AS Curr_Amt,
isNull(es.mtd_hours, 00.00) AS Mtd_Hours,
isNull(es.mtd_amt, 00.00) AS Mtd_Amt,
isNull(es.ytd_hours, 00.00) AS Ytd_Hours,
isNull(es.ytd_amt, 00.00) AS Ytd_Amt,
CAST(ISNULL(es.trans_code,0) AS varchar(10)) AS Trans_Code
FROM dbo.estub es with(nolock)
INNER JOIN
dbo.payroll_grouping pg with (nolock)
ON es.PAYGROUP = pg.PAYGROUP
INNER JOIN
dbo.employee_ext ee with (nolock)
ON es.EMPLOYEE_ID = ee.EMPLOYEE_ID
INNER JOIN
dbo.system_code_table ctpf with (nolock)
ON es.PAYFREQCODE = ctpf.CODE_VALUE
AND ctpf.CODE_TYPE = 'payfreq'
AND ctpf.LANG_TYPE = 1
INNER JOIN
dbo.payroll_period_ext ppe with (nolock)
ON es.PERIOD_NUMBER = ppe.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctct with (nolock)
ON es.CHEQUE_TYPE = ctct.CODE_VALUE
AND ctct.CODE_TYPE = 'chequetype'
AND ctct.LANG_TYPE = 1
INNER JOIN
dbo.system_code_table ctp with (nolock)
ON es.PAY_CODE = ctp.CODE_VALUE
AND ctp.CODE_TYPE = 'paycode'
AND ctp.LANG_TYPE = 1
INNER JOIN
dbo.pay_request pr with (nolock)
ON pr.period_number = es.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctrt with (nolock)
ON pr.RUN_TYPE = ctrt.CODE_VALUE
AND ctrt.CODE_TYPE = 'runtype'
AND ctrt.LANG_TYPE = 1|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uPflI7BAGHA.664@.TK2MSFTNGP10.phx.gbl...
> when you say the joins use 7 of the columns do you mean
> 1) it joins on all 7 columns to each other table
> (table1.col1 = table2.col1 and ... and table1.col7 = table2.col7)
> or
> 2) or that only 7 of the columns are used in any of the joins
> (join 1: table1.col1 = table2.col1, join 2: table1.col1 = table3.col1 and
> table1.col3 = table3.col3, etc.)
> ?
> only if you will join on all 7 columns in at least one join, should you
> create an index on all 7 columns [even then, consider the selectivity]
> otherwise, examine your joins and choose the most selective ones for the
> indexes. also, one index may be good for more than one set of joins -
> e.g., join 1 is on col1 and col2, join 2 is on col1, col2 and col3 -- a
> single index on col1, col2 and col3 will cover both (if col1 and col2 are
> the first 2 columns in the index...)
> also, make sure the tables you are joining to are indexed properly to
> match - otherwise performance won't increase all that much
> [posting DDL for the tables (including existing keys and indexes) and
> views would help]
>
here is the information:
EXEC sp_addtype N'T_CODE', N'int', N'null'
GO
EXEC sp_addtype N'T_VALUE', N'int', N'null'
GO
EXEC sp_addtype N'T_IDENTIFIER', N'numeric(18,0)', N'not null'
GO
EXEC sp_addtype N'T_AMOUNT', N'decimal(10,2)', N'null'
GO
EXEC sp_addtype N'T_AMOUNT4', N'decimal(12,4)', N'null'
GO
EXEC sp_addtype N'T_LONG_NAME', N'varchar (50)', N'null'
GO
CREATE TABLE [ESTUB] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PERIOD_NUMBER] [T_VALUE] NULL ,
[PAYGROUP] [T_CODE] NULL ,
[EMPLOYEE_ID] [T_IDENTIFIER] NULL ,
[LOCATION_CODE] [T_CODE] NULL ,
[DEPARTMENT_CODE] [T_CODE] NULL ,
[CHEQUE_TYPE] [T_CODE] NULL ,
[SEQUENCE] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[CODE_TYPE] [T_CODE] NULL ,
[PAY_CODE] [T_CODE] NULL ,
[CURR_HOURS] [T_AMOUNT] NULL ,
[RATE] [T_AMOUNT4] NULL ,
[CURR_AMT] [T_AMOUNT] NULL ,
[MTD_HOURS] [T_AMOUNT] NULL ,
[MTD_AMT] [T_AMOUNT] NULL ,
[YTD_HOURS] [T_AMOUNT] NULL ,
[YTD_AMT] [T_AMOUNT] NULL ,
[ACCOUNT_NUMBER] [T_LONG_NAME] NULL ,
[SORT_ORDER] [T_VALUE] NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[DISP_STATUS] [T_CODE] NULL ,
[TRANS_CODE] [T_CODE] NULL ,
CONSTRAINT [PK_ESTUB_BCK] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_ESTUB] ON [dbo].[ESTUB]([EMPLOYEE_ID], [PAY_CODE],
[PERIOD_NUMBER], [PAYGROUP], [PAYFREQCODE], [CHEQUE_TYPE]) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_GROUPING] (
[PAYGROUP] [smallint] NOT NULL ,
[COMPANY_ID] [numeric](18, 0) NOT NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PAYFREQCODE] [smallint] NOT NULL ,
CONSTRAINT [PK_PAYROLL_GROUPING] PRIMARY KEY NONCLUSTERED
(
[PAYGROUP]
) WITH FILLFACTOR = 100 ON [PRIMARY] ,
CONSTRAINT [IX_PAYROLL_GROUPING] UNIQUE NONCLUSTERED
(
[COMPANY_ID],
[PAYROLL_NUMBER],
[PAYFREQCODE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_PAYGROUP] ON
[dbo].[PAYROLL_GROUPING]([PAYGROUP]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE TABLE [EMPLOYEE_EXT] (
[EMPLOYEE_ID] [T_IDENTIFIER] NOT NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[JOB] [T_CODE] NULL ,
[ROE_REASON] [T_CODE] NULL ,
[ROE_STATUS] [T_CODE] NULL ,
[WCBCODE] [T_CODE] NULL ,
[SPLITCODE] [T_CODE] NULL ,
[COMMISSION] [T_CODE] NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[EMP_EXTCODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYGROUP] [T_CODE] NULL ,
[DELETED_FLAG] [int] NULL CONSTRAINT [DF_EMPLOYEE_EXT_DELETED_FLAG] DEFAULT
(0),
CONSTRAINT [PK_EMPLOYEE_EXT] PRIMARY KEY NONCLUSTERED
(
[EMPLOYEE_ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SYSTEM_CODE_TABLE] (
[CODE_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODE_VALUE] [int] NOT NULL ,
[LANG_TYPE] [int] NOT NULL ,
[SHORT_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTIVE] [bit] NOT NULL ,
[SORT] [int] NULL ,
[REFERENCE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SYSTEM_CODE_TABLE] PRIMARY KEY NONCLUSTERED
(
[CODE_TYPE],
[CODE_VALUE],
[LANG_TYPE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_PERIOD_EXT] (
[PERIOD_NUMBER] [int] NOT NULL ,
[paygroup_code] [int] NULL ,
[PAYFREQCODE] [int] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[EXT_PERIOD] [smallint] NULL ,
[PAY_CYCLE] [smallint] NULL ,
[DEDUCTION_CYCLE] [smallint] NULL ,
[CHEQUE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
CONSTRAINT [PK_PAYROLL_PERIOD_EXT] PRIMARY KEY CLUSTERED
(
[PERIOD_NUMBER]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAY_REQUEST] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PAYGROUP_CODE] [smallint] NULL ,
[COMPANY_ID] [numeric](18, 0) NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_NUMBER] [int] NULL ,
[REPORT_GENERATE] [numeric](1, 0) NULL ,
[PURGE_FISCALMEMOS] [numeric](1, 0) NULL ,
[RUN_TYPE] [numeric](1, 0) NULL ,
[PRINT_TIMECARDS] [numeric](1, 0) NULL ,
[SPECIAL_REPORTS] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CONSOLIDATION_REPORT] [numeric](1, 0) NULL ,
[USER_FIELD] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FREQUENCY_CODE] [int] NULL ,
[PAY_DATE] [datetime] NULL ,
[NEWPERIOD_CLEAR] [numeric](1, 0) NULL ,
[PAY_CYCLE] [numeric](1, 0) NULL ,
[DEDUCTION_CYCLE] [numeric](1, 0) NULL ,
[GENERATE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
[YEAR_END] [int] NULL ,
CONSTRAINT [PK_PAY_REQUEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_PAY_REQUEST] ON [dbo].[PAY_REQUEST]([PERIOD_NUMBER],
[RUN_TYPE]) ON [PRIMARY]
GO
And the Query is
SELECT
CAST(pg.PAYROLL_NUMBER AS varchar(10)) + '$' + ee.EMP_EXTCODE AS
EmployeeKey,
CAST(pg.PAYROLL_NUMBER AS varchar(10)) AS CompanyId,
CAST(ee.EMP_EXTCODE AS varchar(10)) AS EmployeeId,
ctpf.DESCRIPTION AS Frequency,
YEAR(ppe.cheque_date) as Year,
MONTH(ppe.cheque_date) as Month,
CAST(ppe.EXT_PERIOD AS varchar(10)) AS Period_Number,
CONVERT(varchar(10),ppe.start_date,101) AS Start_Date,
CONVERT(varchar(10),ppe.end_date,101) AS End_Date,
CONVERT(varchar(10),ppe.cheque_date,101) AS Cheque_Date,
ctct.DESCRIPTION AS Cheque_Type,
ctrt.Description AS Run_Type,
CAST(ISNULL(es.sequence,'') AS varchar(10)) AS Sequence,
ctp.EXT_CODE AS Hed,
ctp.DESCRIPTION AS Hed_Description,
isNull(es.curr_hours, 00.00) AS Curr_Hours,
es.rate AS Rate,
isNull(es.curr_amt, 00.00) AS Curr_Amt,
isNull(es.mtd_hours, 00.00) AS Mtd_Hours,
isNull(es.mtd_amt, 00.00) AS Mtd_Amt,
isNull(es.ytd_hours, 00.00) AS Ytd_Hours,
isNull(es.ytd_amt, 00.00) AS Ytd_Amt,
CAST(ISNULL(es.trans_code,0) AS varchar(10)) AS Trans_Code
FROM dbo.estub es with(nolock)
INNER JOIN
dbo.payroll_grouping pg with (nolock)
ON es.PAYGROUP = pg.PAYGROUP
INNER JOIN
dbo.employee_ext ee with (nolock)
ON es.EMPLOYEE_ID = ee.EMPLOYEE_ID
INNER JOIN
dbo.system_code_table ctpf with (nolock)
ON es.PAYFREQCODE = ctpf.CODE_VALUE
AND ctpf.CODE_TYPE = 'payfreq'
AND ctpf.LANG_TYPE = 1
INNER JOIN
dbo.payroll_period_ext ppe with (nolock)
ON es.PERIOD_NUMBER = ppe.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctct with (nolock)
ON es.CHEQUE_TYPE = ctct.CODE_VALUE
AND ctct.CODE_TYPE = 'chequetype'
AND ctct.LANG_TYPE = 1
INNER JOIN
dbo.system_code_table ctp with (nolock)
ON es.PAY_CODE = ctp.CODE_VALUE
AND ctp.CODE_TYPE = 'paycode'
AND ctp.LANG_TYPE = 1
INNER JOIN
dbo.pay_request pr with (nolock)
ON pr.period_number = es.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctrt with (nolock)
ON pr.RUN_TYPE = ctrt.CODE_VALUE
AND ctrt.CODE_TYPE = 'runtype'
AND ctrt.LANG_TYPE = 1