created between two dates. A lot of info is in related tables and the case
table only has the id. So there are quiet some Joins, but that's not the big
problem.
What made my SQL unusable is the fact, that I needed also to look up some
info in the 1:n related LOG table. For example I need to know what support
unit was asked something about a certain case. This unitid is not saved in
the CASE table but I can find it out by looking up a varchar field! (and
doing some string-manipulation) in the LOG table where the log_title (anothe
r
varchar field!) has a certain value. I now do this for every record found in
the CASE table (where caseID is the foreign fey in the LOG table.
But since I have to query some 100-tousend records that gets too slow and I
run into a timeout.
Therefor I need a more performant way of doing this kind of query.
Does anyone have any hints for me.
I have no idea how to rearrange it into one query that could be saved as a
stored procedure where at least two dates and a unitid needed to be variable
s.
Thanks for any hints.To give you a hint on what I need to accomplish here the actual queries.
The base query is like this where the view vAppKometFlowLinesXT could
basically be replaced with the CASES table plus the lookups of the keys in
foreign tables.
SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
FROM vAppKometFlowLinesXT
WHERE ((case_generated > CONVERT(DATETIME, '2005-12-01', 102)) AND
(case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102)) AND
(owner_unit = 98190)) OR
((case_generated > CONVERT(DATETIME, '2005-12-01',
102)) AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
AND
(EXISTS
(SELECT id
FROM vAppKometUnitDownAll
WHERE (start = 98190 AND owner_unit = id)) OR
EXISTS
(SELECT TOP 100 PERCENT log_caseID
FROM tAppKometLog
WHERE (log_userunit = 98190 OR
EXISTS
(SELECT id
FROM
vAppKometUnitDownAll
WHERE
(log_userunit = id))) AND case_id = log_caseID
GROUP BY log_caseID
ORDER BY log_caseID)))
ORDER BY cid
Then I do this 3 queries on all records with the caseID (or cid) as the
log_caseID.
SELECT SUBSTRING(log_text, PATINDEX('%:%', log_text) + 2, LEN(log_text) -
PATINDEX('%:%', log_text)) AS logtextOE, log_userunit
FROM tAppKometLog
WHERE (345414 = log_caseID)
AND (log_title = 'log_questionsent')
ORDER BY tAppKometLog.log_id DESC
SELECT log_ownerunit
FROM tAppKometLog
WHERE (345414 = log_caseID)
AND ((log_userunit = 98190) AND (log_title = 'log_forward'))
ORDER BY tAppKometLog.log_id DESC
SELECT log_userunit
FROM tAppKometLog
WHERE (log_caseID = 345414)
AND ((log_ownerunit = 98190) AND (log_title = 'log_forward'))
ORDER BY tAppKometLog.log_id DESC
I somehow need to get this into a single query.|||"zu" <zu@.discussions.microsoft.com> wrote in message
news:07244D9D-49F6-44B2-B077-79F2F6EE92C1@.microsoft.com...
> To give you a hint on what I need to accomplish here the actual queries.
> The base query is like this where the view vAppKometFlowLinesXT could
> basically be replaced with the CASES table plus the lookups of the keys in
> foreign tables.
> SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
> FROM vAppKometFlowLinesXT
> WHERE ((case_generated > CONVERT(DATETIME, '2005-12-01', 102)) AND
> (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102)) AND
> (owner_unit = 98190)) OR
> ((case_generated > CONVERT(DATETIME, '2005-12-01',
> 102)) AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
> AND
> (EXISTS
> (SELECT id
> FROM vAppKometUnitDownAll
> WHERE (start = 98190 AND owner_unit = id))
> OR
> EXISTS
> (SELECT TOP 100 PERCENT log_caseID
> FROM tAppKometLog
> WHERE (log_userunit = 98190 OR
> EXISTS
> (SELECT id
> FROM
> vAppKometUnitDownAll
> WHERE
> (log_userunit = id))) AND case_id = log_caseID
> GROUP BY log_caseID
> ORDER BY log_caseID)))
> ORDER BY cid
>
> Then I do this 3 queries on all records with the caseID (or cid) as the
> log_caseID.
>
One thing at a time. First rewriting your query to place matchint parens
either on the same line, or vertically aligned, we get
SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
FROM vAppKometFlowLinesXT
WHERE
(
(case_generated > CONVERT(DATETIME, '2005-12-01', 102))
AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
AND (owner_unit = 98190)
)
OR
(
(case_generated > CONVERT(DATETIME, '2005-12-01', 102))
AND (case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102))
AND(
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (start = 98190 AND owner_unit = id)
)
OR
EXISTS
(
SELECT TOP 100 PERCENT log_caseID
FROM tAppKometLog
WHERE (
log_userunit = 98190
OR
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (log_userunit = id)
)
)
AND case_id = log_caseID
GROUP BY log_caseID
ORDER BY log_caseID
)
)
)
ORDER BY cid
Then factor out the date criteria to a top-level AND, and get rid of the
absurd EXISTS (SELECT TOP 100 PERCENT...
SELECT *, category_prefix + CAST(case_id AS Varchar(20)) AS cid
FROM vAppKometFlowLinesXT
WHERE case_generated > CONVERT(DATETIME, '2005-12-01', 102)
AND case_generated < CONVERT(DATETIME, '2005-12-30 23:59:59', 102)
AND
(
owner_unit = 98190
OR
(
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (start = 98190 AND owner_unit = id)
)
OR
EXISTS
(
SELECT *
FROM tAppKometLog
WHERE (
log_userunit = 98190
OR
EXISTS
(
SELECT id
FROM vAppKometUnitDownAll
WHERE (log_userunit = id)
)
)
AND case_id = log_caseID
)
)
)
ORDER BY cid
Try that. If you need more help, decorate all the columns with table
aliases, and post the table DDL.
David|||thanks
this sounds incredible helpful.
I thought that this EXISTS (SELECT TOP 100... might not be best practice.
It's just the way it was.
I didn't expect such a good answer that quick an will take me time too look
at it next year;-)
My family is waiting since it's already quiet late now.
But thank you. Iappreciate it.|||thanks again
hope you started the new year well and happy!
the query doesn't return all I need which obviously is the 'fault' of the
vAppKometFlowLinesXT view, which in itself doesn't return all fields I need.
I rewrote it so that I get the cols I want (as far as the're easy to get) an
have some
significant aliases. The main customer need, to get 4 new fields which I
need to get out of the log is not yet implemented since it's beyond my
current capacity.
The
things are: The query now needs 26 seconds and returns 29357 rowswhile it previously only took 16 second and returned 29347 rows.
Of course you need to have to be able to have a look at the table structure
to see the points.
I really don't know how to send the DDL.
I know how to script the database but I think this is not what you'd need.
Just tell me, if you think I get you too busy or am asking too much.
the vAppKometFlowLinesXT old and new look like this below
/* dbo.vAppKometFlowLinesXT */
SELECT dbo.tAppKometCase.case_generated,
dbo.tAppKometCategory.category_prefix, dbo.tAppKometCase.case_id,
dbo.tAppKometCategory.category_langtextKEY,
dbo.tAppKometType.type_langtextKEY, dbo.tAppKometUnit.unit_name,
dbo.tAppKometUnit.unit_description,
dbo.tAppKometUnit.unit_KST, dbo.tAppKometCase.case_provisorisch,
dbo.tAppKometCase.case_solution,
dbo.tAppKometCase.case_count,
tAppKometUnit_1.unit_description AS owner_description,
dbo.tAppKometCase.case_ownerLOGIN,
dbo.tAppKometState.state_langtextKEY,
dbo.vAppKometInlistProblem.casecoords_value AS list3_problem,
dbo.tAppKometUnit.unit_colonel,
tAppKometUnit_1.unit_id AS owner_unit,
dbo.tAppKometCase.case_weighting, tAppKometUnit_1.unit_description AS
supportoe,
dbo.tAppKometCase.case_bemerkungsupport,
dbo.tAppKometCase.case_eskalation, dbo.vAppKometInlist.casecoords_value AS
list1_desc,
dbo.vAppKometInlist.field_name AS list1_name,
dbo.vAppKometInlist2.field_name AS list2_name,
dbo.vAppKometInlist2.casecoords_value AS list2_desc,
dbo.tAppKometCase.case_done, dbo.tAppKometCase.case_timeset,
dbo.tAppKometCase.case_others,
dbo.tAppKometCase.case_resent, dbo.tAppKometCause.cause_langtextKEY AS
ursache_key,
vtAppKometCauseCat.category_langtextKEY AS katkorr,
vAppKometCauseType.type_langtextKEY AS typekorr,
dbo.tAppKometCase.case_sorti,
dbo.tAppKometSorti.sorti_txt AS sortiment,
dbo.vAppKometInlist2.flabel2, dbo.vAppKometInlist.flabel1,
dbo.vAppKometBoss.casecoords_value AS bossnr,
dbo.tAppKometCase.case_stateID, tAppKometUnit_1.unit_name AS owner_unitname,
dbo.tAppKometCase.case_sapnr
FROM dbo.tAppKometCase INNER JOIN
dbo.tAppKometUnit ON dbo.tAppKometCase.case_userUnitID
= dbo.tAppKometUnit.unit_id INNER JOIN
dbo.tAppKometCategory ON
dbo.tAppKometCase.case_categoryID = dbo.tAppKometCategory.category_id INNER
JOIN
dbo.tAppKometType ON dbo.tAppKometCase.case_typeID =
dbo.tAppKometType.type_id INNER JOIN
dbo.tAppKometUnit tAppKometUnit_1 ON
dbo.tAppKometCase.case_owner_unitID = tAppKometUnit_1.unit_id INNER JOIN
dbo.tAppKometState ON dbo.tAppKometCase.case_stateID =
dbo.tAppKometState.state_id INNER JOIN
dbo.tAppKometCategory vtAppKometCauseCat ON
dbo.tAppKometCase.case_causeCategory = vtAppKometCauseCat.category_id INNER
JOIN
dbo.tAppKometType vAppKometCauseType ON
dbo.tAppKometCase.case_causeType = vAppKometCauseType.type_id LEFT OUTER JOI
N
dbo.vAppKometBoss ON dbo.tAppKometCase.case_id =
dbo.vAppKometBoss.casecoords_caseID LEFT OUTER JOIN
dbo.tAppKometSorti ON dbo.tAppKometCase.case_sorti =
dbo.tAppKometSorti.sorti_id LEFT OUTER JOIN
dbo.tAppKometCause ON dbo.tAppKometCase.case_cause =
dbo.tAppKometCause.cause_id LEFT OUTER JOIN
dbo.vAppKometInlist2 ON dbo.tAppKometCase.case_id =
dbo.vAppKometInlist2.casecoords_caseID LEFT OUTER JOIN
dbo.vAppKometInlist ON dbo.tAppKometCase.case_id =
dbo.vAppKometInlist.casecoords_caseID LEFT OUTER JOIN
dbo.vAppKometInlistProblem ON
dbo.tAppKometCase.case_id = dbo.vAppKometInlistProblem.casecoords_caseID
WHERE (dbo.tAppKometCase.case_caseID IS NULL)
/* dbo.vAppKometFlowLinesXT_NEW20051230 */
SELECT tAppKometCase.case_generated AS ErfasstAm,
tAppKometCase.case_done AS ErledigtAm, tAppKometCategory.category_prefix AS
KategoriePrefix,
tAppKometCase.case_id AS Fallnummer,
OwnerUnit.unit_description AS Supportstelle, tAppKometState.state_langtextKE
Y
AS Status,
UserUnit.unit_description AS ErfasstDurch,
tAppKometCase.case_userLOGIN AS ErfasstDurchName, tAppKometCase.case_sapnr A
S
ErfasserSAPNr,
UserUnit.unit_KST AS KST,
tAppKometCategory.category_langtextKEY AS Kategorie,
tAppKometType.type_langtextKEY AS Problemart,
vtAppKometCauseCat.category_langtextKEY AS katkorr,
vAppKometCauseType.type_langtextKEY AS typekorr,
vAppKometBoss.casecoords_value AS bossnr,
vAppKometInlist.field_name AS Flex1Name, vAppKometInlist.casecoords_value AS
Flex1,
vAppKometInlist2.field_name AS Flex2Name,
vAppKometInlist2.casecoords_value AS Flex2,
vAppKometInlistProblem.casecoords_value AS
Problembeschreibung, tAppKometCause.cause_langtextKEY AS Ursache,
tAppKometCase.case_provisorisch AS ProvLoesung,
tAppKometCase.case_solution AS Loesung, tAppKometCase.case_ownerLOGIN AS
Supporter,
tAppKometCase.case_count AS Anz,
vAppKometInlistMangel.casecoords_value AS Mangel,
vAppKometInlistLieferant.casecoords_value AS Lieferant
FROM tAppKometCase INNER JOIN
tAppKometCategory ON tAppKometCase.case_categoryID =
tAppKometCategory.category_id INNER JOIN
tAppKometType ON tAppKometCase.case_typeID =
tAppKometType.type_id INNER JOIN
tAppKometState ON tAppKometCase.case_stateID =
tAppKometState.state_id INNER JOIN
tAppKometCategory vtAppKometCauseCat ON
tAppKometCase.case_causeCategory = vtAppKometCauseCat.category_id INNER JOIN
tAppKometType vAppKometCauseType ON
tAppKometCase.case_causeType = vAppKometCauseType.type_id LEFT OUTER JOIN
tAppKometUnit UserUnit ON
tAppKometCase.case_userUnitID = UserUnit.unit_id LEFT OUTER JOIN
vAppKometInlistLieferant ON tAppKometCase.case_id =
vAppKometInlistLieferant.casecoords_caseID LEFT OUTER JOIN
vAppKometInlistMangel ON tAppKometCase.case_id =
vAppKometInlistMangel.casecoords_caseID LEFT OUTER JOIN
vAppKometBoss ON tAppKometCase.case_id =
vAppKometBoss.casecoords_caseID LEFT OUTER JOIN
tAppKometCause ON tAppKometCase.case_cause =
tAppKometCause.cause_id LEFT OUTER JOIN
vAppKometInlist2 ON tAppKometCase.case_id =
vAppKometInlist2.casecoords_caseID LEFT OUTER JOIN
vAppKometInlist ON tAppKometCase.case_id =
vAppKometInlist.casecoords_caseID LEFT OUTER JOIN
vAppKometInlistProblem ON tAppKometCase.case_id =
vAppKometInlistProblem.casecoords_caseID LEFT OUTER JOIN
tAppKometUnit OwnerUnit ON
tAppKometCase.case_owner_unitID = OwnerUnit.unit_id
WHERE (tAppKometCase.case_caseID IS NULL)
the tables case and log have the following structure:
CREATE TABLE [dbo].[tAppKometCase] (
[case_id] [numeric](18, 0) IDENTITY (100000, 1) NOT NULL ,
[case_caseID] [numeric](18, 0) NULL ,
[case_typeID] [int] NOT NULL ,
[case_categoryID] [int] NOT NULL ,
[case_userFullname] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[case_userLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[case_userUnitID] [int] NOT NULL ,
[case_ownerLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_owner_unitID] [int] NOT NULL ,
[case_worker_unitID] [int] NULL ,
[case_workerLogin] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_workerconditionID] [int] NULL ,
[case_workerattachement] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[case_count] [int] NOT NULL ,
[case_generated] [datetime] NOT NULL ,
[case_eskalation] [datetime] NULL ,
[case_eskalation_set] [tinyint] NOT NULL ,
[case_stateID] [int] NOT NULL ,
[case_provisorisch] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_attachprovisorisch] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[case_solution] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_attachsolution] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[case_bemerkungsupport] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_reason] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[case_notconfirmed] [bit] NOT NULL ,
[case_cause] [int] NOT NULL ,
[case_causeType] [int] NOT NULL ,
[case_causeCategory] [int] NOT NULL ,
[case_enterLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_enterUnitID] [int] NULL ,
[case_rule_set] [tinyint] NOT NULL ,
[case_sapnr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[case_weighting] [smallint] NULL ,
[case_resent] [bit] NULL ,
[case_others] [bit] NULL ,
[case_timeset] [int] NULL ,
[case_done] [datetime] NULL ,
[case_sorti] [smallint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tAppKometLog] (
[log_id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[log_title] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[log_desc] [varchar] (300) COLLATE Latin1_General_CI_AS NULL ,
[log_text] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[log_caseID] [numeric](18, 0) NULL ,
[log_timestamp] [datetime] NULL ,
[log_userLOGIN] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[log_userunit] [int] NULL ,
[log_userFullName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[log_owner] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[log_ownerunit] [int] NULL ,
[log_state] [int] NULL
) ON [PRIMARY]
the rest of the tables involve are basically just foreign key look ups.
If this helps you to help me, that would be great. Otherwise I'd love to
hear what would help even just to get more hints on how to go on myself.
No comments:
Post a Comment