|
|
Posted by Dan Guzman on 09/27/06 02:03
> Dan, my uniqueid column (named [objectid]) is the only key, and at
> least at the moment, this column is set as primary key (ok? not ok?).
> My protection to duplicate entries is handled by the client
> application. 99% of the business logic is concentrated in client app.
> The server side only minimally processes the request and deals with the
> database (select/update/insert/delete). I fully understand that I have
> to logically split my tables using other indexes.
You probably intended to direct this question at Hugo rather than me but my
$.02 is that you should also have a unique constraint on you natural key.
Even if integrity is enforced in the application, the database must still be
queried based on the natural key value. The unique constraint creates a
unique index that will improve performance of that query and guarantee data
integrity as well.
> ORDER BY il.itemid, il.stockdate, d.doctypekey, i.docnumber,
> il.create_date, ilb.create_date
I'm not sure why the ORDER BY here. Does your current processing logic
require that rows be processed in a particular sequence?
> I want to change this in the following manner: to create an temporary
> table and to insert the results of the above SELECT statement into this
> temp table. After that, using a cursor, I intend to fetch the records
> from the temp table. This way, the tables involved in the SELECT stm
> above will be locked as short as possible. Do you think this is a good
> approach?
The temp table will probably help mitigate blocking but you ought to avoid
cursors entirely, if possible. A set-based process usually performs much
better, especially if cursors are nested. If you must use a cursor, specify
the LOCAL FAST_FORWARD READ_ONLY when possible.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"nano2k" <adi@ikonsoft.ro> wrote in message
news:1159306705.223359.38470@e3g2000cwe.googlegroups.com...
> Hugo, Dan
> Thanks for your patience with this topic.
>
> I understand and agree to all your advices.
>
> My struggle, at least for the moment, is to make the application as
> much responsive as possible while strongly looking for data security.
> Let's say that for the moment we don't care how much the SP needs to
> process - we care only to let other users work while the SP is running.
> The SP will be run only a few times a month, but there are 2-3 fixed
> days of the month when the SP needs to perform - it's all about
> accounting :) I only want to give other users the ability to work while
> the SP runs.
>
> I need uniqueidentifiers because my app is a three-tier client-server
> app (client/server/db) that needs to generate unique IDs from client
> side. The client app may generate several records from different tables
> that will be sent all together in a single request to the server. The
> records need to be already coupled (that is, foreign keys must be
> already known before sending the request to the database) before
> sending them to the server to process. Of course, there is an issue
> here regarding indexing this column, as Dan mentioned. Anyway, I have
> set a task to reindex the database every night, so I think this will
> reduce the payback to using uniqueidentifiers - do you agree? Is there
> any other action I should schedule?
>
> Dan, my uniqueid column (named [objectid]) is the only key, and at
> least at the moment, this column is set as primary key (ok? not ok?).
> My protection to duplicate entries is handled by the client
> application. 99% of the business logic is concentrated in client app.
> The server side only minimally processes the request and deals with the
> database (select/update/insert/delete). I fully understand that I have
> to logically split my tables using other indexes.
>
> Dan, you got right in the heart of my problem when you said: "In
> general terms: you need to ensure that "old" invoices and "new"
> invoices can be completely seperated". This is my goal at the moment.
> Your inputs along with Hugo's inputs helped me to start my research on
> the right path.
>
> But today, I have discovered that the SP heavily uses CURSORS :((
> This is the other big issue I have to deal with. Check out this piece
> of code (one of 3 CURSORS defined in SP):
>
> DECLARE inputs CURSOR FOR
> SELECT i.accountid, il.doctypeid,
> il.objectid,
> (CASE WHEN ilb.objectid IS NOT NULL THEN ilb.objectid ELSE il.objectid
> END),
> il.itemid, ilb.batchid, d.updatestock * d.cogssign * (CASE WHEN
> ilb.objectid IS NOT NULL THEN ilb.pdocquantity ELSE il.pdocquantity
> END),
> d.updatestock * d.cogssign * il.price * il.rate * (1 - il.discount /
> 100) * (1 - i.discount / 100),
> il.currencyid, il.rate,
> il.warehouseid, dbo.f_date_notime(il.stockdate)
> FROM
> inventory i
> JOIN inventoryline il ON (il.inventoryid = i.objectid)
> JOIN item it ON (it.objectid = il.itemid)
> JOIN doctype d ON (il.doctypeid = d.objectid)
> JOIN status st ON (st.objectid = i.statusid)
> JOIN warehouse w ON (w.objectid = il.warehouseid)
> LEFT JOIN inventorylinebatch ilb ON (ilb.inventorylineid = il.objectid)
> WHERE
> d.cogssign = 1
> AND (st.final = 1 OR st.cancel = 1)
> AND d.doctypekey NOT IN ('25','26')
> AND il.stockdate >= dbo.f_date_notime(@last_date) + 1
> AND it.stockcontrol = 1
> AND ISNULL(w.nocost,0) = 0
> ORDER BY il.itemid, il.stockdate, d.doctypekey, i.docnumber,
> il.create_date, ilb.create_date
>
> OPEN inputs
>
> ...follows fetching results in a loop
>
> I want to change this in the following manner: to create an temporary
> table and to insert the results of the above SELECT statement into this
> temp table. After that, using a cursor, I intend to fetch the records
> from the temp table. This way, the tables involved in the SELECT stm
> above will be locked as short as possible. Do you think this is a good
> approach?
>
> Note: In the SELECT statement above: ALL tables involved have a primay
> key defined as uniqueidentifier and named objectid. Also, ALL foreign
> keys (FK) are indexed like this:
>
> CREATE INDEX [FK_inventoryline_inventoryid] ON
> [dbo].[inventoryline]([inventoryid]) ON [PRIMARY]
> GO
>
>
> Thanks again.
>
Navigation:
[Reply to this message]
|