I neeed to search through the CASE table and get all records with case
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 (another
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 variables.

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.

Show quote Hide quote 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 sad things are: The query now needs 26 seconds and returns 29357 rows
while 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 JOIN
                      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_langtextKEY
AS Status,
                      UserUnit.unit_description AS ErfasstDurch,
tAppKometCase.case_userLOGIN AS ErfasstDurchName, tAppKometCase.case_sapnr AS
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.

sql statement formatting
Use of NULL and DEFAULT
Record locking within a stored procedure
compare '002' and '00²'
Evaluation of conditions