locked
Item Position RRS feed

  • Question

  • Hi 

    I have a database table which log the item movement e.g from production to warehouse 1 and to warehouse 2 or another place and so on

    The table layout like below

    |  ID - Unique  |  ItemID  |  From  |  To  |

    the ID is unique, the itemid is not and repeatable since one item can move 3-6 time

    The table now have 4 million row or more, and I have trouble finding an item list in specific warehouse ( very slow query )

    Is there a better way to handle this or better query ( we use select all item with a [To] = warehouseid and since they can have multiple same [To] -- return goods -- after the select we have to reselect the biggest id using max which create many loops and expensive )

     

    Saturday, February 19, 2011 12:42 PM

Answers

  • Please post sample data to test it (in terms of different rows)

    Your plan looks different from the query posted here, am I right? create NCI on typeid column and [to] column

    select * from cu_cutfactdetail cucfd,zc_manage zcm where cucfd.id = zcm.materialid and cucfd.ifuse = 6 and zcm.[to] = 47 and zcm.[id] in ( select max(id) id from zc_manage group by materialid) option(maxdop 1)

    or see my vwersion

    with cte
    as
    (
     select materialid, max(id) id from zc_manage group by materialid
    )select * from cu_cutfactdetail cucfd
       join zc_manage zcm
      on  cucfd.id = zcm.materialid
       join cte on cte.id =zc_manage.id
      where cucfd.ifuse = 6
    and zcm.[to] = 47

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 6, 2011 9:37 AM

All replies

  • Hi Wiryono,

    Please post table schema including the indexes and your query/code here.


    Thank you!

    My blog: http://aboutsqlserver.com

    Saturday, February 19, 2011 4:12 PM
  • ItemMovement: - we have more than 4000000 rows

    ID - Unique (int) , MaterialID (int), From (int), To (int), Movement Date(DateTime)

    ItemList: - we have more than 800000 rows , the status is using code 1 to 6 which already packaged, still in production and so on

    ID - Unique (int), Weight(int), Height(int), Width(int), CreateDate (DateTime) , Status(Int)

    Note: no foreign keys

     

    We have id for the warehouse stock, this id is use in the ItemMovement table [From] and [To]

    1-Warehouse 1

    2-Warehouse 2

    3-Warehouse 3

    4-Already Sold

     

    Let's say i check the warehouse 1 stock, this is the query

    SELECT * FROM ItemMovement itmm INNER JOIN ItemList itml

    on itml.id = itmm.materialid

    where itml.status = 6 // This means already packaged

    and itmm.[to] = 1 //movement to warehouse 1

    and itmm.id = ( select max(id) from ItemMovement where materialid = itmm.materialid ) //Create a lot of loops

     

    Our warehouse 1 only have 15000 more item ( physically and in data  )

     

     

    Sunday, February 20, 2011 3:59 AM
  • I think we need a specific table that record the actual place of the item

    It works and faster from minute to 1-2 seconds on query

     

    The item movement will always be add ( INSERT only TSQL ) so to update i create a trigger after insert to update it on the new table

    After the test the new table is record correctly. Hooray

     

    But I got more problem the add process ( INSERT ) to the item movement table is done after many query inside a single transaction / procedure.

    When I run the transaction the trigger break everything including itself ( not update the new table ) and transaction is not working 

     

    I guess after adding new item to the table the next query in the transaction require some data from the last insert. which is break by my trigger. Is it possible ?

    Or does trigger work separately ?

     

     

     

     

    Thursday, March 3, 2011 3:38 AM
  • Could you post DDL of your tables and what all indexes those tables have?

    I think a better indexing and partitioning could solve your problem.

    ~Manu
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Thursday, March 3, 2011 4:55 AM
  • --SELECT * FROM ItemMovement itmm INNER JOIN ItemList itml

    --on itml.id = itmm.materialid

    --where itml.status = 6 // This means already packaged

    --and itmm.[to] = 1 //movement to warehouse 1

    --and itmm.id = ( select max(id) from ItemMovement where materialid = -i---tmm.materialid ) //Create a lot of loops

     

    You say that above query run slowly? To speed up the query you need properly defined indexes.. So , do you have an unique index on ID column, Materialid column?

    In order to provide you with accurate suggestion please post exacution plan of the query 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 3, 2011 6:26 AM
  • Any progress?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 6, 2011 8:11 AM
  • The index only in ID in every table ( the pk ) clustered

     

    I just guest that below sql is the culprit, when i exclude this it ran fast :D 

    and itmm.id = ( select max(id) from ItemMovement where materialid = itmm.materialid ) 

     

    Actually my table is bigger than this not just two table. I just try to minimize to the core problem.

     

    Here is the real execution plan:

    hmmm- how do i put picture in this forum ??

    Sunday, March 6, 2011 8:33 AM
  • They can show in the table mode

     
     |--Parallelism(Gather Streams)
           |--Nested Loops(Inner Join, OUTER REFERENCES:([CU_CutFactDetail].[FilmTypeID]))
                |--Hash Match(Left Semi Join, HASH:([c].[ID])=([Expr1002]), RESIDUAL:([c].[ID]=[Expr1002]))
                |    |--Bitmap(HASH:([c].[ID]), DEFINE:([Bitmap1009]))
                |    |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([c].[ID]))
                |    |         |--Hash Match(Inner Join, HASH:([CU_CutFactDetail].[ID])=([c].[MaterialID]))
                |    |              |--Parallelism(Broadcast)
                |    |              |    |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[MaterialID]) WITH PREFETCH)
                |    |              |         |--Parallelism(Gather Streams)
                |    |              |         |    |--Clustered Index Scan(OBJECT:([tspc].[dbo].[ZC_Manage].[PK_ZC_MANAGE] AS [a]), WHERE:([a].[To]=67 AND (([a].[TypeID]=111 OR [a].[TypeID]=110) OR [a].[TypeID]=109)))
                |    |              |         |--Clustered Index Seek(OBJECT:([tspc].[dbo].[CU_CutFactDetail].[PK_CU_CUTFACTDETAIL]), SEEK:([CU_CutFactDetail].[ID]=[a].[MaterialID]),  WHERE:([CU_CutFactDetail].[StockID]=67 AND [CU_CutFactDetail].[IFUse]=5) ORD
                |    |              |--Clustered Index Scan(OBJECT:([tspc].[dbo].[CU_Storage].[PK_CU_Storage] AS [c]), WHERE:([c].[StockID]=67))
                |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Expr1002]), WHERE:(PROBE([Bitmap1009])=TRUE))
                |         |--Hash Match(Aggregate, HASH:([CU_Storage].[MaterialID]) DEFINE:([Expr1002]=MAX([CU_Storage].[ID])))
                |              |--Parallelism(Repartition Streams, PARTITION COLUMNS:([CU_Storage].[MaterialID]))
                |                   |--Clustered Index Scan(OBJECT:([tspc].[dbo].[CU_Storage].[PK_CU_Storage]))
                |--Clustered Index Seek(OBJECT:([tspc].[dbo].[IN_FilmTypeS].[PK_IN_FILMTYPES] AS [INF]), SEEK:([INF].[ID]=[CU_CutFactDetail].[FilmTypeID]) ORDERED FORWARD)


    Ok Let me explain the table:

    Cu_CutFactDetail is the same as itemList Table
    ZC_Manage is the item movement log table
    CU_Storage is grade movement log
    > After changing to different warehouse it might broke and the grade might go down  
    > Same as zc_manage unique id , duplicate materialid, many grade 
    IN_FILMTYPES
    > This just a type reference table nothing big only 200 rows.

    In here the zc_manage ( the item movement log ) is not grouped yet using the max(id) ....
    only the grade change log, and it also heavy...

    after this query done the next one will group the item movement log by max(id).





    Sunday, March 6, 2011 8:51 AM
  • See if this works better

    SELECT * FROM ItemMovement itmm
    INNER JOIN ItemList itml
    on itml.id = itmm.materialid
    join (select materialid, max(id) id from ItemMovement
    group by materialid) as der on
     der.materialid = itmm.materialid
    where itml.status = 6
    and itmm.[to] = 1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 6, 2011 8:51 AM
  • Hmm 

    I convert it to my real table:

     

    This is from you :

    select * from zc_manage zcm

    inner join cu_cutfactdetail cucfd

    on cucfd.id = zcm.materialid

    join 

    ( select materialid, max(id) id from zc_manage group by materialid ) as mxzcm

    on mxzcm.materialid = zcm.materialid

    where cucfd.ifuse = 6

    and zcm.[to] = 47

     

    This is the sql from the software 

    select * from cu_cutfactdetail cucfd,zc_manage zcm

    where cucfd.id = zcm.materialid

    and cucfd.ifuse = 6

    and zcm.[to] = 47

    and zcm.[id] in ( select max(id) id from zc_manage group by materialid)

     

    Yours is faster, but different result :(.

     

     

    Sunday, March 6, 2011 9:14 AM
  • Please post sample data to test it (in terms of different rows)

    Your plan looks different from the query posted here, am I right? create NCI on typeid column and [to] column

    select * from cu_cutfactdetail cucfd,zc_manage zcm where cucfd.id = zcm.materialid and cucfd.ifuse = 6 and zcm.[to] = 47 and zcm.[id] in ( select max(id) id from zc_manage group by materialid) option(maxdop 1)

    or see my vwersion

    with cte
    as
    (
     select materialid, max(id) id from zc_manage group by materialid
    )select * from cu_cutfactdetail cucfd
       join zc_manage zcm
      on  cucfd.id = zcm.materialid
       join cte on cte.id =zc_manage.id
      where cucfd.ifuse = 6
    and zcm.[to] = 47

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 6, 2011 9:37 AM