oh so slow query...

The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?

Thanks in advance. Clive

SELECT
T1.APPT_REPT_FLG,
T18.X_ALIS_ID,
CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
T1.ASGN_USR_EXCLD_FLG,
T2.NAME,
T19.STAT_CD,
T1.APPT_REPT_TYPE,
T15.NAME,
CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
T1.TODO_CD,
T1.X_DOC_CAT_ID,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
T1.TARGET_OU_ID,
T7.ZIPCODE,
T3.ZIPCODE,
T9.EXP_RPT_NUM,
T1.LAST_UPD_BY,
T1.OWNER_PER_ID,
T1.PART_RPR_ID,
T1.RATE_LST_ID,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.ACTIVITY_UID,
T4.NAME,
T1.PR_TMSHT_LINE_ID,
T18.LAST_NAME,
T7.ADDR,
T18.SEX_MF,
T1.BILLABLE_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.SRA_SR_ID,
T1.TARGET_PER_ADDR_ID,
T18.X_FST_NAME,
T1.EVT_STAT_CD,
CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
T1.ROW_STATUS,
T1.ACD_CALL_DURATION,
T5.NAME,
T8.FAX_PH_NUM,
T8.X_FST_NAME,
T8.LAST_NAME,
T1.MODIFICATION_NUM,
T1.X_CAMP_ID,
CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
T1.ASSOCIATED_COST,
T13.NAME,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
T17.TMSHT_NUM,
T1.PR_SYMPTOM_CD,
T1.OPTY_ID,
CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
T1.PR_EXP_RPT_ID,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
T8.FST_NAME,
T16.SR_NUM,
T1.SRA_DEFECT_ID,
T1.CREATED_BY,
T8.WORK_PH_NUM,
CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
T1.CALL_ID,
T1.X_CLIENT_ID,
T1.PROJ_ID,
T12.DEFECT_NUM,
T1.CREATOR_LOGIN,
T1.CONFLICT_ID,
T19.OUTCOME_CD,
T1.TEMPLATE_FLG,
T2.PR_ADDR_ID,
T1.PREV_ACT_ID,
T1.X_DOC_NAME,
T1.EXP_RLTD_FLG,
T1.X_BATCH_REF,
T1.PRI_LST_ID,
T1.SRC_ID,
T1.X_POLICY_REF,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.EMAIL_FORWARD_FLG,
T11.DMT_NUM,
T1.TMSHT_RLTD_FLG,
T1.ROW_ID,
T10.NAME,
T18.CONSUMER_FLG,
T1.TARGET_PER_ID,
T18.FST_NAME,
T1.PRIV_FLG,
T3.PROVINCE,
T8.X_ALIS_ID,
T8.JOB_TITLE,
T14.NAME,
T1.NAME,
T1.PCT_COMPLETE,
T1.SRA_TYPE_CD,
T1.ALARM_FLAG,
T1.CAL_DISP_FLG,
T1.EVT_PRIORITY_CD,
T1.COST_CURCY_CD,
T2.LOC,
CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
T20.FILE_NAME,
T1.SRA_RESOLUTION_CD,
T6.PRDINT_ID,
T1.OWNER_LOGIN
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
WHERE
((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_SR_ID = '1-EQLOO')ummmm...nothing?

Seriously though...I think I'd break it up...

Looks like the driver is the aliased table T1...

Make a dervide table out of that and get it as small as possible, then do your joins...

You need to help us with DDL though...esp. the indexes...

Ya gotta love surrogate keys...|||I think I've realised what's going on. The query in question is generated by the application (app sever probably). However, it's not a stored procedure. The query is exactly the same every time it is executed with one exception - a single id value changes. That will mean compilation every time the query is made I guess. I was noticing this as I was trying out a derived table approach and I was using Profiler to see if any improvements were made. Running any version of the query for the first time in QA took around 2 minutes but subequent exectutions were a few seconds. I am assuming that if the application vendor rewrote that part of their app to call a stored procedure with the id value as a parameter, it would run a lot more quickly without all those compilations.

Thanks,

Clive|||Either sp or prepared statement would do. In either case the plan should be reused, though I'd prefer sp.

Office Web Component Pivottable functionality

I use the Office Web Component 2003 Pivottable in SharePoint but there are some bad behaviours.

- slow loading

- error messages like: An Error 0x0040200 occured

We use Reporting Services in most cases, but the positive aspects like Dynamic Dimensions and measure usage or Dynamic filtering in Hierarchies are to import to lose.

The Office Web Components are not developed anymore. In SharePoint Services 3.0 they can function, but it is not 100% the correct way. Does someone know the best way to use Pivottables in SharePoint Services 3.0?

Thnx

Hessel Appers

Hi,

I am using OWC 11 and it works perfectly for me,

although I am not using sharepoint, but have my own Asp.Net portal.

I am using OWC pivottables and pivotcharts.

OWC should be as fast as your BI studio/visual studio 2005 while loading/displaying or browsing a cube. (I believe)

because BI studio or Visual studio uses OWC.

If the performance is slower in both the cases, then it might be that you need to improve performance of your cube.

if the loading time is slower, try to display a filtered dataset or a higher level dataset,

the users can then drill down to the level required.

When using OWC, it is always better to show only the most required data and also the most minimum set.

This is because OWC is browser based, it would make IE take up a lot of memory,

and thus make IE slower, eventually your OWC component would also become slow with more data.

As an alternative to using OWC pivottables,

is to use the newer excel services (Microsoft Office 2005).

It jells best with sharepoint and is infact built to support sharepoint.

another alternative is to try other third party and free OLAP controls.

one free control that I can suggest is the "CellSetGrid".

The source code is also avaliable,

so you can look through it and make changes if required.

also the simple usage is pretty easy.

search for 'CellSetGrid' in google.

please do let me know,

how this comes, what you choose and what gave you best performance and features.

Regards

ODBC vs OLE DB

The ODBC processing via DTS is slower than OLE DB which resulted following
inquiries:
- Why it so slow (ODBC)?
- Any idea how to overcome it
I use ODBC to communicate between IBM AS/400 DB2 and MS-SQL Server 2000.
Please advise.
Thanks.Might be your ODBC driver that is not performing OK, check out the
StarQuest products (www.starquest.com), they have ODBC drivers and
fast DB2 to SQL Server replication solutions.
Bob
"samuel" <samuellai@.ajikl.com.my> wrote in message news:<O05WtWbAEHA.3824@.TK2MSFTNGP09.phx.
gbl>...
> The ODBC processing via DTS is slower than OLE DB which resulted following
> inquiries:
> - Why it so slow (ODBC)?
> - Any idea how to overcome it
> I use ODBC to communicate between IBM AS/400 DB2 and MS-SQL Server 2000.
> Please advise.
> Thanks.

ODBC Timeout expired error

Some of my clients cannot access an application, and it seems there is
a communication issue(?). The application server is a bit slow, the
database server (MS SQL Server 2000, SQL SP4) is brand new and fast.
Users that have the problem are behind a firewall that allows
everything from the client network to the server network. I have
configured the ODBC DSN, tested it, and the test succeeds. When
starting the application however, they get an error, [Microsoft][ODB
C
SQL Server Driver]Timeout expired. I have traced the startup, and the
log file is pasted below:
Vbus 100-938 ENTER SQLAllocEnv
HENV * 013FC478
Vbus 100-938 EXIT SQLAllocEnv with return code 0
(SQL_SUCCESS)
HENV * 0x013FC478 ( 0x02031540)
Vbus 100-938 ENTER SQLAllocConnect
HENV 02031540
HDBC * 01AC4ED0
Vbus 100-938 EXIT SQLAllocConnect with return code 0
(SQL_SUCCESS)
HENV 02031540
HDBC * 0x01AC4ED0 ( 0x020315e8)
Vbus 100-938 ENTER SQLDriverConnectW
HDBC 020315E8
HWND 001902C6
WCHAR * 0x74329A38 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x74329A38
SWORD 2
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
Vbus 100-938 EXIT SQLDriverConnectW with return code -1
(SQL_ERROR)
HDBC 020315E8
HWND 001902C6
WCHAR * 0x74329A38 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x74329A38
SWORD 2
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Timeout expired
(0)
Vbus 100-938 ENTER SQLErrorW
HENV 02031540
HDBC 020315E8
HSTMT 00000000
WCHAR * 0x00208B48 (NYI)
SDWORD * 0x01AC4EDC
WCHAR * 0x00208748
SWORD 512
SWORD * 0x0020F08C
Vbus 100-938 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV 02031540
HDBC 020315E8
HSTMT 00000000
WCHAR * 0x00208B48 (NYI)
SDWORD * 0x01AC4EDC (0)
WCHAR * 0x00208748 [ 50] "[Microsoft][ODBC SQL
Server Driver]Timeout expired"
SWORD 512
SWORD * 0x0020F08C (50)
It seems that the SQLDriverConnectW is what generates the error, but I
am not a database administrator, nor developer, and knows nothing about
what it is or means.
The ODBC-driver (odbc32.dll) version on the machines that get this
error, is identical to those that don't.
Can anyone interpret this trace for me? What goes on, what is the
ODBC-driver (or anything else) complaining about? Would be highly
appreciated!
Bjorn Henrik Formo.Any luck on this issue. I am running into the same error. The ODBC test
connects fine but from my application I cannot.
-Markus
markusr
---
markusr's Profile: http://www.dbtalk.net/m568
View this thread: http://www.dbtalk.net/t169537

ODBC Inserts are very slow

Hi,

I am new to the windows world. We use Informatica on UNIX for ETL process. We have a requirement to load approx. 200,000 rows to a MS SQL Server table . The table is not that big and it is a heap table (no indexes). Inserts are taking 69 rows/per minute. We are using DataDirect Closed 4.10 SQL Server ODBC driver.

SQL Profiler tells us that is is doing a row by row processing and using sp_execute procedure.

Is there a way we can speed up the ODBC process?

-Thanks in advance
srv

SQL Server Version:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)That would appear to be an older version of Informatica (pre-2003 installation). There was a problem that was fixed late in the year (2003-09 I think).

As a work-around, have Informatica generate a flat text file (tab delimited is my preference, columnar is often simplest, CSV is tolerable). FTP that file to your NT machine, then use DTS or BCP to load the file into SQL Server. You should have no trouble getting 10000 rows per minute from a mid-range workstation, and could get 1000000 rows per minute on a sufficiently beefy server.

-PatP|||If possible use DTS in SQL server which used BULK LOAD process for such load and for further information refer to the books online or http://www.sqldts.com website.
HTH|||We performed a test with Informatica 7.x and same odbc drivers and it 4 times faster. --Thanks