Dear all,
i was confuse that izzit there are sql server was support for loop
statement,
i got one section of inner query in my asp program, and i would like
to change in to stored procedure,i using ms sql server as my database.
the following is the asp code with the inner sql
For i = 1 To 10
If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
strSQL = "INSERT INTO ack_item
(Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
&",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
&"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
CheckComma(Request.Form("txtQty" & i)) &","
strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
call SetConnection(strSQL,2)
End If
Next
could i change whole code into the sql server (store procedure)?Hi
Yep , there is a WHILE loop in the SQL Server
DECLARE @.i INT
SET @.i=1
WHILE @.i<=10
BEGIN
--Do something here
SET @.i=@.i+1
END
Can you elaborate a little bit what you are doing so we can suggest a
solution without using a loop?
<yokesanhoo@.gmail.com> wrote in message
news:1140499360.016330.300370@.g44g2000cwa.googlegroups.com...
> Dear all,
> i was confuse that izzit there are sql server was support for loop
> statement,
> i got one section of inner query in my asp program, and i would like
> to change in to stored procedure,i using ms sql server as my database.
> the following is the asp code with the inner sql
> For i = 1 To 10
> If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
> strSQL = "INSERT INTO ack_item
> (Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
> &",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
> &"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
> CheckComma(Request.Form("txtQty" & i)) &","
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
> call SetConnection(strSQL,2)
> End If
> Next
> could i change whole code into the sql server (store procedure)?
>
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
IX locks question
From what I'm getting, IX locks are just a safety mechanism to allow a
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >
> > From what I'm getting, IX locks are just a safety mechanism to allow a
> > query
> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
> > needs
> > to
> > escalate row locks to page or table locks. Is this a correct
assumption?
> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
> > X locks on the rows affected and IX locks on the pages and on the table?
> > Is there a sample example against pubs or Northwind somewhere online
that
> > illustrates this?
> >
> >
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_con_7a_5ovi.htm).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> To state it another way, an IX lock means that an exclusive lock may be
> held
>> at a lower level. For example, a row level exclusive lock will also
> acquire
>> a table level IX lock. The IX lock will prevent a conflicting
>> table-level
>> lock from being acquired without having to check individual locks that
>> are
>> lower in the hierarchy.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
>> >
>> > From what I'm getting, IX locks are just a safety mechanism to allow a
>> > query
>> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
>> > needs
>> > to
>> > escalate row locks to page or table locks. Is this a correct
> assumption?
>> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
>> > X locks on the rows affected and IX locks on the pages and on the
>> > table?
>> > Is there a sample example against pubs or Northwind somewhere online
> that
>> > illustrates this?
>> >
>> >
>>
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> > Am I close?
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> > I'm sorry Dan, I'm having trouble understanding. Thanks for bearing
with
> > me.
> >
> > Is IX lock a safety mechanism of the lock manager?
> >
> > This is what I think occurs based on my reading and toying around with
> > pubs...
> > please correct me where I'm wrong.
> >
> > Process 1 deletes a row from the authors table. An X lock is placed on
> > the
> > row being
> > deleted. IX is applied at the page and the table levels to signal to
the
> > lock manager that there's a lower
> > level lock because something's going on at a page or row level.
> >
> > Now, Process 2 comes along to delete rows from the authors table. the
> > lock
> > manager says "Hold on, there
> > are lower level row locks that must be checked before you may proceed".
> > At
> > that point, it checks
> > to see if the rows it's deleting will cause a page or table lock. If
so,
> > Process 2 will wait because
> > Process 1 already has an IX lock. If no page or table lock will be
> > needed,
> > Process 2 will place
> > X locks on the rows affected and also place it's own IX lock at the page
> > and
> > table level.
> >
> > Am I close?
> >
> > TIA
> > Dave
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> >> To state it another way, an IX lock means that an exclusive lock may be
> > held
> >> at a lower level. For example, a row level exclusive lock will also
> > acquire
> >> a table level IX lock. The IX lock will prevent a conflicting
> >> table-level
> >> lock from being acquired without having to check individual locks that
> >> are
> >> lower in the hierarchy.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >> >
> >> > From what I'm getting, IX locks are just a safety mechanism to allow
a
> >> > query
> >> > to lock at a higher grain (i.e. say, a table lock) if the lock
manager
> >> > needs
> >> > to
> >> > escalate row locks to page or table locks. Is this a correct
> > assumption?
> >> > So, everytime I perform a DELETE or UPDATE can I expect that I will
get
> >> > X locks on the rows affected and IX locks on the pages and on the
> >> > table?
> >> > Is there a sample example against pubs or Northwind somewhere online
> > that
> >> > illustrates this?
> >> >
> >> >
> >>
> >>
> >
> >
>
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >
> > From what I'm getting, IX locks are just a safety mechanism to allow a
> > query
> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
> > needs
> > to
> > escalate row locks to page or table locks. Is this a correct
assumption?
> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
> > X locks on the rows affected and IX locks on the pages and on the table?
> > Is there a sample example against pubs or Northwind somewhere online
that
> > illustrates this?
> >
> >
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_con_7a_5ovi.htm).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> To state it another way, an IX lock means that an exclusive lock may be
> held
>> at a lower level. For example, a row level exclusive lock will also
> acquire
>> a table level IX lock. The IX lock will prevent a conflicting
>> table-level
>> lock from being acquired without having to check individual locks that
>> are
>> lower in the hierarchy.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
>> >
>> > From what I'm getting, IX locks are just a safety mechanism to allow a
>> > query
>> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
>> > needs
>> > to
>> > escalate row locks to page or table locks. Is this a correct
> assumption?
>> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
>> > X locks on the rows affected and IX locks on the pages and on the
>> > table?
>> > Is there a sample example against pubs or Northwind somewhere online
> that
>> > illustrates this?
>> >
>> >
>>
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> > Am I close?
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> > I'm sorry Dan, I'm having trouble understanding. Thanks for bearing
with
> > me.
> >
> > Is IX lock a safety mechanism of the lock manager?
> >
> > This is what I think occurs based on my reading and toying around with
> > pubs...
> > please correct me where I'm wrong.
> >
> > Process 1 deletes a row from the authors table. An X lock is placed on
> > the
> > row being
> > deleted. IX is applied at the page and the table levels to signal to
the
> > lock manager that there's a lower
> > level lock because something's going on at a page or row level.
> >
> > Now, Process 2 comes along to delete rows from the authors table. the
> > lock
> > manager says "Hold on, there
> > are lower level row locks that must be checked before you may proceed".
> > At
> > that point, it checks
> > to see if the rows it's deleting will cause a page or table lock. If
so,
> > Process 2 will wait because
> > Process 1 already has an IX lock. If no page or table lock will be
> > needed,
> > Process 2 will place
> > X locks on the rows affected and also place it's own IX lock at the page
> > and
> > table level.
> >
> > Am I close?
> >
> > TIA
> > Dave
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> >> To state it another way, an IX lock means that an exclusive lock may be
> > held
> >> at a lower level. For example, a row level exclusive lock will also
> > acquire
> >> a table level IX lock. The IX lock will prevent a conflicting
> >> table-level
> >> lock from being acquired without having to check individual locks that
> >> are
> >> lower in the hierarchy.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >> >
> >> > From what I'm getting, IX locks are just a safety mechanism to allow
a
> >> > query
> >> > to lock at a higher grain (i.e. say, a table lock) if the lock
manager
> >> > needs
> >> > to
> >> > escalate row locks to page or table locks. Is this a correct
> > assumption?
> >> > So, everytime I perform a DELETE or UPDATE can I expect that I will
get
> >> > X locks on the rows affected and IX locks on the pages and on the
> >> > table?
> >> > Is there a sample example against pubs or Northwind somewhere online
> > that
> >> > illustrates this?
> >> >
> >> >
> >>
> >>
> >
> >
>
I've never seen this before
I saw something today that I had never noticed before. A collegue of mine
and I were troubleshooting a query that was not performing as expected. The
query consisted of a Select from a complex view with a where in subquery.
In all, the query was taking about 30s. The subquery runs quickly when run
by itself. Heck, the Select from the views run quickly by itself.
We looked at the execution plan to try to figure out where we might be able
to add effeciencies. 16% of the total work was being done by the subquery.
This didn't seem to make sense. Then we noticed that the subquery data was
being joined into data stream as the view was being built! I guess I
expected the Optimizer to build the view, run the subquery, and loop through
the data to filter it. The Optimizer instead redefined the view on the fly
by joining in the subquery data. To test it we did a Select Into a temp
table with * from the view and then ran a second query filtering that data
according to the subquery. That test took 6 seconds.
Has anyone seen this type of behaviour before?
ChristianSQL Server has always implemented queries against views by combining
the SELECT that is the view with the SELECT that references the view.
Then it is up to the optimizer to make a good query plan out of that.
There are times the optimizer doesn't choose well, and a query
referencing a complex view such as you describe is certainly a prime
candicate for this problem.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 10:34:42 -0400, "Christian Smith"
<malekai101@.yahoo.com> wrote:
>I saw something today that I had never noticed before. A collegue of mine
>and I were troubleshooting a query that was not performing as expected. Th
e
>query consisted of a Select from a complex view with a where in subquery.
>In all, the query was taking about 30s. The subquery runs quickly when run
>by itself. Heck, the Select from the views run quickly by itself.
>We looked at the execution plan to try to figure out where we might be able
>to add effeciencies. 16% of the total work was being done by the subquery.
>This didn't seem to make sense. Then we noticed that the subquery data was
>being joined into data stream as the view was being built! I guess I
>expected the Optimizer to build the view, run the subquery, and loop throug
h
>the data to filter it. The Optimizer instead redefined the view on the fly
>by joining in the subquery data. To test it we did a Select Into a temp
>table with * from the view and then ran a second query filtering that data
>according to the subquery. That test took 6 seconds.
>Has anyone seen this type of behaviour before?
>Christian
and I were troubleshooting a query that was not performing as expected. The
query consisted of a Select from a complex view with a where in subquery.
In all, the query was taking about 30s. The subquery runs quickly when run
by itself. Heck, the Select from the views run quickly by itself.
We looked at the execution plan to try to figure out where we might be able
to add effeciencies. 16% of the total work was being done by the subquery.
This didn't seem to make sense. Then we noticed that the subquery data was
being joined into data stream as the view was being built! I guess I
expected the Optimizer to build the view, run the subquery, and loop through
the data to filter it. The Optimizer instead redefined the view on the fly
by joining in the subquery data. To test it we did a Select Into a temp
table with * from the view and then ran a second query filtering that data
according to the subquery. That test took 6 seconds.
Has anyone seen this type of behaviour before?
ChristianSQL Server has always implemented queries against views by combining
the SELECT that is the view with the SELECT that references the view.
Then it is up to the optimizer to make a good query plan out of that.
There are times the optimizer doesn't choose well, and a query
referencing a complex view such as you describe is certainly a prime
candicate for this problem.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 10:34:42 -0400, "Christian Smith"
<malekai101@.yahoo.com> wrote:
>I saw something today that I had never noticed before. A collegue of mine
>and I were troubleshooting a query that was not performing as expected. Th
e
>query consisted of a Select from a complex view with a where in subquery.
>In all, the query was taking about 30s. The subquery runs quickly when run
>by itself. Heck, the Select from the views run quickly by itself.
>We looked at the execution plan to try to figure out where we might be able
>to add effeciencies. 16% of the total work was being done by the subquery.
>This didn't seem to make sense. Then we noticed that the subquery data was
>being joined into data stream as the view was being built! I guess I
>expected the Optimizer to build the view, run the subquery, and loop throug
h
>the data to filter it. The Optimizer instead redefined the view on the fly
>by joining in the subquery data. To test it we did a Select Into a temp
>table with * from the view and then ran a second query filtering that data
>according to the subquery. That test took 6 seconds.
>Has anyone seen this type of behaviour before?
>Christian
Ive never been good with relationships
I'm having some trouble working out how to query some data. Rather than explain up front, here's some examples of what I want to achieve:
*******************************************************
I've got a structure which looks vaguely like this:
[ANCESTORS]
Grandparent
Parent
Child
If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Grandparent.Name ='Cybill'
This would return all of the children of 'Cybill' and their children. Now, if I use the following query:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Child.Name ='Jean'
This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).
Likewise, if I enter:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Parent.Name ='Ron'
Then I would get Ron's parents and also his children.
*******************************************************
So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.
Can anybody see my dilemma here?
Mark-up ASP.net posts here
MarkItUp.com... no. I don't. I've done something like this before with someone, and we didn't go about that method. :(
What's the problem with doing inner joins?|||Because, with Inner JOINs I am relying upon the existance of children to be able to return the grandparents.|||I actually have one table "Ancestors" and also a separate linking table which contains the relationships:
[Ancestors] ( id int, Name varchar )
[Relationships] ( id, fkid )
What I need to be able to do is to write a single query which can span "up to 7" lines of descendency, that is:
Great-Great-Great-Grandparent
Great-Great-Grandparent
Great-Great-Grandparent
Great-Grandparent
Grandparent
Parent
Child
I just have absolutely no idea how to write a single sql query which could filter on one or more levels but ensure referential integrity down the line. In other words, I can specify a child which would trace up the tree in a single line, or, specify a Great-Great-Great-Grandparent which would span out from a single point and would show all the way down to leaf nodes regardless of which level they finished at.|||Can we use a UDF? :-)|||Yes, I'm using SqlServer 2K.
I have finally tuned the stored procedure "program" enough to get my - previously 14 hour (give or take 100 milliseconds) - query down to sub-20 seconds. This is good enough to ship to the client so, I'm going to go with what I have for now.|||20 seconds? I'm still not believing that to be good enough. :) but if it's good enough for the client, it's good enough I guess.
If you're using any temp tables or what not, try indexing them before you apply data to them. I trimmed a procedure that executed in 12 seconds to 2.|||KraGiE, I agree, 20 seconds is a long time to wait, but, it's working so that's a much better position than I had 2 days ago.
I do have # temp tables, about 12 of them actually (more on that later), and my final optimization was to add indexes and optimization hints in the appropriate places, this reduced a 2 minute 20 second query to sub 30 seconds.
As for the 12 temp tables, this, to me was a fair indicator that the schema of the database was wrong to begin with so now, I 'm actually pushing to have the db schema altered too. I'm hoping that this will give me enough of a foothold to be able to achieve the necessary remaining improvements|||Personally, I'm a big fan of int based look up tables. :) Well, in cases like this, I'm fond of them because you can branch them out as far as you want without having just 'add a new field'.
I also think loops are my best friend when used properly.
What I meant by the indexes (if this is what you're doing already, then I'm a redundant moron) is ...
|||Yeh, that's pretty much what I'm doing already re: the indexes ( although who am I to call you a redundant moron :P ) except that I create the index only after population and, therefore also set the fill factor to 100% - but, for the purposes of an illustration, yes, that's pretty much what I do.|||awesome. In some cases, I'll put the index in before I start populating if the data's going to be enormous. It sets a bit faster from my experience.
SET NOCOUNT ONCREATE TABLE #TempTable
(
tableID int IDENTITY,
TableField varchar(50)
SomeOtherVar int
)
GOCREATE INDEX idx_Temp
ON #TempTable ( tableID )
GOINSERT INTO #TempTable
SELECT
TableID,
TableField,
SomeOtherVar
FROM
RealTable
-- WHERE Your CriteriaGO
-- Do Other Calculations
TRUNCATE TABLE #TempTable
DROP INDEX idx_Temp
DROP TABLE #TempTableGO
-- Yes Terri, I space my sql in procedures and QA this way normally, and I'm just lazy when I post here. :)
Ive been given a query analizer for MSDE are there others
Greetings,
I've been sent an alternative "Query Analizer" thingy to play with and it seems to do the job - but it ain't my area.
I've posted the tool in a free public place for a short time:
http://www.adoanywhere.com/members/yeohray/7C1_SQLTool.zip
But, for the benefit of people reding this from the archives, please follow
the registerable author link [ http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1&TPN=1 ]
Apart from needing documentation (we all know about that old scenario) it seems good.
It didn't waste my time (and I wouldn't waste yours I hope). Could I have your comments please ?
Also, I'm looking for other tools that do the same as this one - so I can compare functionality. Even without try the freebie, any links please ?
TIAyou've "been sent"? by whom? where's it from? did you virus-check it?
okay, i tried it (yes, i took a chance, i'm stupid that way)
it actually works nicely -- a damn site better at submitting queries to MSDE than the way i was doing it up till now (via a pass-through query in access)
it's cute, too|||Good point about the security!
I'll address that issue with the author, virus scanners here should have picked them up though.
I got it from a chap I was assisting with some Delphi knowledge.
Thanks for checking it out, I didn't understand all of it myself.|||so, this isn't yours, then? i mean, it isn't adoanywhere's? don't they have something similar? how come you posted it on their forum? do you work for adoanywhere? do they mind you mentioning what appears to be a competing product? just what does their product do, anyway? and where did you say you got this little Delphi app, in case it screws somethying up on my machine and i feel like visiting the said chap at his place of residence with a clue-by-four?
:D|||Ther author was having problems with bugs in Delphi and retrieval of stats & plans through ADO. With adoanywhere forum help he eventually got it resolved and posted the tool for us to play with. I've used it witout problems since it was uploaded but I'll make it clearer in future posts and on the forum that all these third party apps are at downloaders risk.
I work for adoanwyhere - I wrote adoanywhere, and it's no problem adding other tools to forum if you know of any. Glad to 'ave 'em.
Mike (adoanywhere)|||okay, thanks for the clarification
by the way, does your product allow me to run queries against MSDE too?
i should mention i haven't a clue what ADO is (and no particular desire to find out)|||Yeah, you can query MSDE with adoanywhere.
ADO is an interface (bit like ODBC), so you can query databases that have and ADO "driver" - called provider in ado lingo.
Most databses have an ADO provider. Problem is though that ADO has loads of properties are that may or may not be available at any give time in your application.
Adoanywhere lets you fiddle with the ado settings. So although you can query data with it, it does more complex stuff at its heart.
I generally use adoanywhere for querying & checking ado properties - then switch to proprietry tools (like query analizer and Enterprise manager) for database management stuff. Horses for courses as they say.
All the best, Mike.sql
I've been sent an alternative "Query Analizer" thingy to play with and it seems to do the job - but it ain't my area.
I've posted the tool in a free public place for a short time:
http://www.adoanywhere.com/members/yeohray/7C1_SQLTool.zip
But, for the benefit of people reding this from the archives, please follow
the registerable author link [ http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1&TPN=1 ]
Apart from needing documentation (we all know about that old scenario) it seems good.
It didn't waste my time (and I wouldn't waste yours I hope). Could I have your comments please ?
Also, I'm looking for other tools that do the same as this one - so I can compare functionality. Even without try the freebie, any links please ?
TIAyou've "been sent"? by whom? where's it from? did you virus-check it?
okay, i tried it (yes, i took a chance, i'm stupid that way)
it actually works nicely -- a damn site better at submitting queries to MSDE than the way i was doing it up till now (via a pass-through query in access)
it's cute, too|||Good point about the security!
I'll address that issue with the author, virus scanners here should have picked them up though.
I got it from a chap I was assisting with some Delphi knowledge.
Thanks for checking it out, I didn't understand all of it myself.|||so, this isn't yours, then? i mean, it isn't adoanywhere's? don't they have something similar? how come you posted it on their forum? do you work for adoanywhere? do they mind you mentioning what appears to be a competing product? just what does their product do, anyway? and where did you say you got this little Delphi app, in case it screws somethying up on my machine and i feel like visiting the said chap at his place of residence with a clue-by-four?
:D|||Ther author was having problems with bugs in Delphi and retrieval of stats & plans through ADO. With adoanywhere forum help he eventually got it resolved and posted the tool for us to play with. I've used it witout problems since it was uploaded but I'll make it clearer in future posts and on the forum that all these third party apps are at downloaders risk.
I work for adoanwyhere - I wrote adoanywhere, and it's no problem adding other tools to forum if you know of any. Glad to 'ave 'em.
Mike (adoanywhere)|||okay, thanks for the clarification
by the way, does your product allow me to run queries against MSDE too?
i should mention i haven't a clue what ADO is (and no particular desire to find out)|||Yeah, you can query MSDE with adoanywhere.
ADO is an interface (bit like ODBC), so you can query databases that have and ADO "driver" - called provider in ado lingo.
Most databses have an ADO provider. Problem is though that ADO has loads of properties are that may or may not be available at any give time in your application.
Adoanywhere lets you fiddle with the ado settings. So although you can query data with it, it does more complex stuff at its heart.
I generally use adoanywhere for querying & checking ado properties - then switch to proprietry tools (like query analizer and Enterprise manager) for database management stuff. Horses for courses as they say.
All the best, Mike.sql
itzik ben-gan
I am using the following query which I found from a fragmanet of code
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Becuase records have more than three AKA names, I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? very
important issue.
2. while assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) just curiouse(
not very important)
SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333First, examine the 65,000 T-SQL in Query Analyzer using the Show Execution
Plan feature and confirm that the select portion of the query is using
efficient index s
s.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
Also, when inserting, updating, or deleting a massive amount of data (ex > 1
million rows), you start running into series issues with the CPU, I/O, and
disk storage consumed by transaction logging. I assure you that attempting
to update 800 million rows in a single batch will take much longer than 4
hours, regardless of your server configuration. Do a goole search of the
*sqlserver* newsgroups using the keywords "transaction log" "million" and
"hours". There are techniques for minimizing transaction logging and
performing the updates or inserts in batches using a looping method. You
will also need to coordinate with the network admin and allocate the storage
space on the SAN ahead of time.
http://groups.google.com/groups? as...n
um=100
Once done, there are also the issues of logical index fragmentation and
physical extent fragmentation, etc.
Don't fire this up on a Friday evening and expect it to run over the
wend; it could be a w long project at best.
<jacob.dba@.gmail.com> wrote in message
news:1141687843.303092.247700@.i39g2000cwa.googlegroups.com...
>I am using the following query which I found from a fragmanet of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Becuase records have more than three AKA names, I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
> 1. is there any other feasible and faster way to do this? very
> important issue.
> 2. while assigning group number, it doesn't give sequential numbers. It
> skips some of the numbers( group Number 1,2 5,9...) just curiouse(
> not very important)
> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO
> distinct firstname, lastname and SSN table from the tu_people_data.
> I created this table to increase the query performance.
> CREATE TABLE [distFLS] (
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> sample data
> there is a column count_id after the tu_id and before fname(tu_id and
> count_id are primary keys)
> tu_id fname lname SSN
> 156078480 KRISINA WALSH 999999000
> 156078480 KRISTINA GIERER 999999000
> 156078480 KRISTINA WALSH 999999000
> 151257883 J SOTO 999999111
> 151257883 JOSE LARIOS 999999111
> 151257883 JOSE SOTO 999999111
> 151257883 L SOTO 999999111
> 136312525 ELADIO GARCIA 999999222
> 136312525 ELADIO NAVA 999999222
> 136312525 ELADIO NAVAGARCIA 999999222
> 136312525 GARCIA NAVA 999999222
> 149180940 DARREN SAUERWINE 999999333
> 149180940 DARREN SUAERWIN 999999333
>
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Becuase records have more than three AKA names, I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? very
important issue.
2. while assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) just curiouse(
not very important)
SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333First, examine the 65,000 T-SQL in Query Analyzer using the Show Execution
Plan feature and confirm that the select portion of the query is using
efficient index s
s.http://msdn.microsoft.com/library/d... />
1_5pde.asp
Also, when inserting, updating, or deleting a massive amount of data (ex > 1
million rows), you start running into series issues with the CPU, I/O, and
disk storage consumed by transaction logging. I assure you that attempting
to update 800 million rows in a single batch will take much longer than 4
hours, regardless of your server configuration. Do a goole search of the
*sqlserver* newsgroups using the keywords "transaction log" "million" and
"hours". There are techniques for minimizing transaction logging and
performing the updates or inserts in batches using a looping method. You
will also need to coordinate with the network admin and allocate the storage
space on the SAN ahead of time.
http://groups.google.com/groups? as...n
um=100
Once done, there are also the issues of logical index fragmentation and
physical extent fragmentation, etc.
Don't fire this up on a Friday evening and expect it to run over the
w
end; it could be a w long project at best.<jacob.dba@.gmail.com> wrote in message
news:1141687843.303092.247700@.i39g2000cwa.googlegroups.com...
>I am using the following query which I found from a fragmanet of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Becuase records have more than three AKA names, I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
> 1. is there any other feasible and faster way to do this? very
> important issue.
> 2. while assigning group number, it doesn't give sequential numbers. It
> skips some of the numbers( group Number 1,2 5,9...) just curiouse(
> not very important)
> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO
> distinct firstname, lastname and SSN table from the tu_people_data.
> I created this table to increase the query performance.
> CREATE TABLE [distFLS] (
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> sample data
> there is a column count_id after the tu_id and before fname(tu_id and
> count_id are primary keys)
> tu_id fname lname SSN
> 156078480 KRISINA WALSH 999999000
> 156078480 KRISTINA GIERER 999999000
> 156078480 KRISTINA WALSH 999999000
> 151257883 J SOTO 999999111
> 151257883 JOSE LARIOS 999999111
> 151257883 JOSE SOTO 999999111
> 151257883 L SOTO 999999111
> 136312525 ELADIO GARCIA 999999222
> 136312525 ELADIO NAVA 999999222
> 136312525 ELADIO NAVAGARCIA 999999222
> 136312525 GARCIA NAVA 999999222
> 149180940 DARREN SAUERWINE 999999333
> 149180940 DARREN SUAERWIN 999999333
>
itzik ben-gan
I am using the following query which I found from a fragmanet of code
by itzik ben-gan to assign a common group id for group of records in my
by itzik ben-gan to assign a common group id for group of records in my
case which have similar SSN and first Name and Last Name. if the SSN is
the same it should also check the first name and last name of the
record. Becuase records have more than three AKA names, I need to check
all the possibilities of first name last name combination to verify the
records are the same.
This code works fine and can assign group numbers for all the rows.
I am trying this code on a database of 65,000 rows. It's taking around
20 minute to complete. but I'll have to run the same code on
800,000,000 rows.
It will take years to finish.
even if the query is optimized to run in 1 second for 65,000 rows, it
will take more than 4 hours to run on the 800,000,000 row. This where I
realized I am in the "wrong jungle".
1. is there any other feasible and faster way to do this? very
important issue.
2. while assigning group number, it doesn't give sequential numbers. It
skips some of the numbers( group Number 1,2 5,9...) just curiouse(
not very important)
SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS grp_num
into tmp_FLS
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
GO
distinct firstname, lastname and SSN table from the tu_people_data.
I created this table to increase the query performance.
CREATE TABLE [distFLS] (
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sample data
there is a column count_id after the tu_id and before fname(tu_id and
count_id are primary keys)
tu_id fname lname SSN
156078480 KRISINA WALSH 999999000
156078480 KRISTINA GIERER 999999000
156078480 KRISTINA WALSH 999999000
151257883 J SOTO 999999111
151257883 JOSE LARIOS 999999111
151257883 JOSE SOTO 999999111
151257883 L SOTO 999999111
136312525 ELADIO GARCIA 999999222
136312525 ELADIO NAVA 999999222
136312525 ELADIO NAVAGARCIA 999999222
136312525 GARCIA NAVA 999999222
149180940 DARREN SAUERWINE 999999333
149180940 DARREN SUAERWIN 999999333Back to basics. The most commn errors in numeric codes liek SSN are
1) missing digit
2) extra digit
3) one wrong digit
4) Pairwise transpose
For names, use Metaphone and write it in a better 3GL than T-SQL.|||i'm not a huge fan of the method of group number assignment. seems
pretty inefficient to do a row count every time.
Try this;
select distinct left(c1.fname,1) as fname, left(c1.lname,1) as lname,
c1.ssn into tmpgroups from tu_people_data
Using SQL, add a new column to tmpgroups which is an identity column
named tu_id
I'm a little puzzled on the business logic. It looks like the records
are identical if the ssn is the same, and the first letter of first
name OR first letter of last name is same. Is this what you want?|||you can add the identity in one shot
select distinct identity(int,1,1) as tu_id,left(c1.fname,1) as fname,
left(c1.lname,1) as lname,
c1.ssn into tmpgroups from tu_people_data
http://sqlservercode.blogspot.com/|||(jacob.dba@.gmail.com) writes:
> I am using the following query which I found from a fragmanet of code
> by itzik ben-gan to assign a common group id for group of records in my
> case which have similar SSN and first Name and Last Name. if the SSN is
> the same it should also check the first name and last name of the
> record. Becuase records have more than three AKA names, I need to check
> all the possibilities of first name last name combination to verify the
> records are the same.
> This code works fine and can assign group numbers for all the rows.
> I am trying this code on a database of 65,000 rows. It's taking around
> 20 minute to complete. but I'll have to run the same code on
> 800,000,000 rows.
> It will take years to finish.
> even if the query is optimized to run in 1 second for 65,000 rows, it
> will take more than 4 hours to run on the 800,000,000 row. This where I
> realized I am in the "wrong jungle".
I hope that the query is not going to be run in a regular fashion on
those 800 million rows, but once you are there, it will be a one-off.
For the problem as given it sounds like a nightmare to process 800
million rows.
> SELECT c1.fname, c1.lname, c1.ssn , c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS grp_num
> into tmp_FLS
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
> GO
Your definition of distFLS does not have an index, at least you did not
post one. A clustered index on ssn would be a good start. In the same
vein, add an index on (ssn, fnmae, lname) on TU_PeopleData.
That may at least speed up your test case on 65000 rows. Although, you
probably need more tweaks to do the 800 million.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have a table with first name, last name, SSN and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. It will take years.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
Doug wrote:
> i'm not a huge fan of the method of group number assignment. seems
> pretty inefficient to do a row count every time.
> Try this;
> select distinct left(c1.fname,1) as fname, left(c1.lname,1) as lname,
> c1.ssn into tmpgroups from tu_people_data
> Using SQL, add a new column to tmpgroups which is an identity column
> named tu_id
> I'm a little puzzled on the business logic. It looks like the records
> are identical if the ssn is the same, and the first letter of first
> name OR first letter of last name is same. Is this what you want?|||I have a table with first name, last name, SSN and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. It will take years.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
Doug wrote:
> i'm not a huge fan of the method of group number assignment. seems
> pretty inefficient to do a row count every time.
> Try this;
> select distinct left(c1.fname,1) as fname, left(c1.lname,1) as lname,
> c1.ssn into tmpgroups from tu_people_data
> Using SQL, add a new column to tmpgroups which is an identity column
> named tu_id
> I'm a little puzzled on the business logic. It looks like the records
> are identical if the ssn is the same, and the first letter of first
> name OR first letter of last name is same. Is this what you want?
Wednesday, March 28, 2012
iterative query
any body know how i can transform a recursive query to iterative one?
if possible i need a simple example witth little explanation
Thanks alot.Please provide a sample of what you have now so that it would be easier to help you.|||
ok thanks before any thing,
it is for learnning purposes, i really have no example, but i have heard about it, so if possible if you can provide a simple scenario. with the conversion
thanks alot.
Monday, March 26, 2012
Iterate result set inside stored procedure
Hello,
I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).
How can I process result rows inside a stored procedure?
This is what I have so far:
CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@.serviceDetailIDint,
@.commentvarchar(500),
@.commentDateDateTime,
@.commentAuthorvarchar(100)
AS
BEGIN
DECLARE @.serviceIDint
DECLARE @.p_recipientsvarchar(8000)
DECLARE @.p_messagevarchar(8000)
DECLARE @.p_subjectvarchar(100)/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @.serviceID = Service_idFROM lm_Service_DetailWHERE Service_Detail_id = @.serviceDetailID/* Get email addresses of Service Responsible Parties */SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_DetailINNERJOIN dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN dbo.lm_Responsible_Party_Filing_TypeON
dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)/* Build message */SET @.p_subject = "KLM - Service ID: " +CAST(@.serviceIDAS varchar(4))
SET @.p_recipients = ""/*need string of addresses*/SET @.p_message = @.p_message + "Service Detail ID: " +CAST(@.serviceDetailIDAS varchar(4)) +char(13)
SET @.p_message = @.p_message + "Comment Date: " +CAST(@.commentDateAs varchar(25)) +char(13)
SET @.p_message = @.p_message + "Comment Author: " + @.commentAuthor +char(13)
SET @.p_message = @.p_message + "Comment: " + @.comment +char(13)PRINT "subject: " + @.p_subject +char(13)
PRINT "recip: " + @.p_recipients +char(13)
PRINT "msg: " + @.p_message +char(13)/*Send the email*/Execute master..xp_sendmail @.recipients = @.p_recipients, @.message = @.p_message, @.subject = @.p_subject
ENDGO
Hi,
You can declare a variable and append the results onto it for each row with:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + dbo.lm_Responsible_Party.Email
FROM xxxxxx
This gives you the email addresses in comma delimted form:
"me@.me.com, you@.you.com, www.this.com"
The only caveat is that I don't believe you can use the DISTINCT with this. So you would need to amend your SELECT so that it does not use this.
You could do this with:
SELECT @.emails = isnull(@.emails,'') + ', ' + Email
FROM
( SELECT DISTINCT dbo.lm_Responsible_Party.Email AS Email
FROM dbo.lm_Service_DetailINNERJOIN
xxxx
) subquery
|||
Awesome, thanks! I got the concatenation working; and you were right about the DISTINCT command. So, when I tried the way you suggested (putting DISTINCT in select below), I kept getting errors. It didn't like the statement. So, any other ideas on how to retrieve only unique values?
Also, I get a leading comma - how do I avoid that on the first entry?
Ex: "recip: , xxx@.xxx.com, yyy@.yyy.com"
|||
I got it! Needed to assign a derived table.
Still have the comma issue, though.
/* Get email addresses of Service Responsible Parties */|||
SELECT @.emails =isnull(@.emails,'') +', ' + tmpEmail
FROM (
SELECT DISTINCT dbo.lm_Responsible_Party.EmailAS tmpEmail
FROM dbo.lm_Service_DetailINNERJOIN
dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
dbo.lm_Responsible_Party_Filing_TypeON
dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID))AS derivedtbl_1
The leading comma is probably because you may have assigned a value to the variable first?
like:
DECLARE @.emails varchar(1000)
SET @.emails=''
The SQL I gave you accounts for it being null the first time round and puts an empty string instead:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + Email
if you can't get rid of it, then use the SUBSTRING function to get rid of the first character.
As for error on the DISTINCT i suggested, it should work, I wrote some sample stuff at my end.
I'll try my explanation in full.. try copying this: (note that you have to name the subquery and you have to alias the Email)
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + PartyEmail
FROM
(SELECT DISTINCT dbo.lm_Responsible_Party.Email AS PartyEmail
FROM dbo.lm_Service_DetailINNERJOIN
dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
dbo.lm_Responsible_Party_Filing_TypeON
dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
) subquery
|||
Just seen your post after I did one!
Excellent news that you got it working.
The comma again is coming from the fact that you have probably assigned "recip:" before you do the select.
Add it on afterwards with
SET @.emails = 'recip: ' + @.emails
Instead of
SELECT @.email=ISNULL(@.email,'') + ',' + {your field}
use
SELECT @.email=CASE WHEN @.email IS NULL THEN '' ELSE @.email+',' END + {your field}
|||
I figured it out, the comma had to be inside the ISNULL command (otherwise it was displaying it regardless of the value of @.emails).
Like this: SELECT @.emails = ISNULL(@.emails + ', ' ,'') + tmpEmail
And, yeah - you were right about the distinct. I didn't realize "subquery" was part of the actual query. I thought you were just referencing the remaining queries.
Either way, thanks a lot for the help - it's good to go now!
items in list A that dont appear in list B (was "Simple Query...I think")
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?OK so it wasnt' so simple...at least MY solution isn't:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC sp_get_trolley_inconsistencies
@.grower CHAR(2),
@.load_id INT
AS
DECLARE @.ID int,
@.TrolleyList varchar(300),
@.Date DATETIME
--Get the relevant date from the DB Table
SELECT @.Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @.load_id)
SET @.TrolleyList = ''
--Get the list of values into a comma delimited string
DECLARE crs_Trolleys CURSOR
FOR SELECT DISTINCT ldl_trolley
FROM load_detail_lines
WHERE ldl_requi_load_id = @.load_id
OPEN crs_Trolleys
FETCH NEXT FROM crs_Trolleys INTO @.ID
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.TrolleyList = @.TrolleyList+CAST(@.ID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Trolleys INTO @.ID
END
SET @.TrolleyList = SUBSTRING(@.TrolleyList,1,DATALENGTH(@.TrolleyList)-2)
CLOSE crs_Trolleys
DEALLOCATE crs_Trolleys
--Parse the string and run the 'IN' statement on each of the Parsed Values
DECLARE @.parsingList VARCHAR(300)
DECLARE @.find_comma INT
DECLARE @.trolleytocheck VARCHAR(4)
SELECT @.parsingList = @.TrolleyList
WHILE @.parsingList IS NOT NULL
BEGIN
SELECT @.find_comma = PATINDEX('%,%',@.parsingList)
IF @.find_comma <> 0
BEGIN
SELECT @.trolleytocheck = SUBSTRING(@.parsingList,1,(@.find_comma-1))
SELECT @.parsingList = LTRIM(SUBSTRING(@.parsingList,(@.find_comma+1),300))
PRINT @.trolleytocheck
IF @.trolleytocheck IN(SELECT distinct ldl_trolley
FROM load_detail_lines, requi_load
WHERE ldl_requi_load_id = rl_id
AND ldl_requi_load_id NOT LIKE @.load_id
AND rl_status = 1
AND DAY(rl_eta) = DAY(@.Date)
AND MONTH(rl_eta) = MONTH(@.Date)
AND YEAR(rl_eta) = YEAR(@.Date))
BEGIN
RAISERROR 50001 'Error! You screwed up, trolley '
END
CONTINUE
END
ELSE
BEGIN
--this block finds the last value in the trolley list
SELECT @.trolleytocheck = @.parsingList
SELECT @.parsingList = null
PRINT @.trolleytocheck
BREAK
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
If anyone can suggest anything more elegant...please let me know.|||Why not join the tables and count the rows that match?|||Doh!
*Commits ritual suicide*
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?OK so it wasnt' so simple...at least MY solution isn't:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC sp_get_trolley_inconsistencies
@.grower CHAR(2),
@.load_id INT
AS
DECLARE @.ID int,
@.TrolleyList varchar(300),
@.Date DATETIME
--Get the relevant date from the DB Table
SELECT @.Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @.load_id)
SET @.TrolleyList = ''
--Get the list of values into a comma delimited string
DECLARE crs_Trolleys CURSOR
FOR SELECT DISTINCT ldl_trolley
FROM load_detail_lines
WHERE ldl_requi_load_id = @.load_id
OPEN crs_Trolleys
FETCH NEXT FROM crs_Trolleys INTO @.ID
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.TrolleyList = @.TrolleyList+CAST(@.ID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Trolleys INTO @.ID
END
SET @.TrolleyList = SUBSTRING(@.TrolleyList,1,DATALENGTH(@.TrolleyList)-2)
CLOSE crs_Trolleys
DEALLOCATE crs_Trolleys
--Parse the string and run the 'IN' statement on each of the Parsed Values
DECLARE @.parsingList VARCHAR(300)
DECLARE @.find_comma INT
DECLARE @.trolleytocheck VARCHAR(4)
SELECT @.parsingList = @.TrolleyList
WHILE @.parsingList IS NOT NULL
BEGIN
SELECT @.find_comma = PATINDEX('%,%',@.parsingList)
IF @.find_comma <> 0
BEGIN
SELECT @.trolleytocheck = SUBSTRING(@.parsingList,1,(@.find_comma-1))
SELECT @.parsingList = LTRIM(SUBSTRING(@.parsingList,(@.find_comma+1),300))
PRINT @.trolleytocheck
IF @.trolleytocheck IN(SELECT distinct ldl_trolley
FROM load_detail_lines, requi_load
WHERE ldl_requi_load_id = rl_id
AND ldl_requi_load_id NOT LIKE @.load_id
AND rl_status = 1
AND DAY(rl_eta) = DAY(@.Date)
AND MONTH(rl_eta) = MONTH(@.Date)
AND YEAR(rl_eta) = YEAR(@.Date))
BEGIN
RAISERROR 50001 'Error! You screwed up, trolley '
END
CONTINUE
END
ELSE
BEGIN
--this block finds the last value in the trolley list
SELECT @.trolleytocheck = @.parsingList
SELECT @.parsingList = null
PRINT @.trolleytocheck
BREAK
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
If anyone can suggest anything more elegant...please let me know.|||Why not join the tables and count the rows that match?|||Doh!
*Commits ritual suicide*
item master and office stock details (was "Query Problem")
Hi,
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER
-----
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E
STOCKDETAILS
-----------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
--------------
FOR OFFICE - 1
--------------
ITEMCODE ITEMNAME OFFICEID
--------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
Thanks in advanceWelcome to the Forum.
Check this...
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = '1') AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1|||Hi Rudra !
how to get such all result in view and then i will query for officeid to get my desired output.|||Hi r937 !
I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?|||Hi r937 !
I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?
Why don't you use stored proc and pass parameter using OFFICEID ? I think that would be a better way to deal with your problem.But I am not sure what your requirement is...|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1
hmm,always ahead...;)|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?
if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?
my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?
if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?
my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?
I suggest you to use stored proc,its always good to use stored proc
in your case.Just write this...
CREATE PROCEDURE dbo.StockView(
@.officeid VARCHAR(20)
AS
--Use mine or Rudy's one
--this is mine
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = @.officeid) AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME
--OR use Rudy's one
select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = @.officeid
Go
And check BOL to use Stored proc in dataset .Its very easy man and better to use in many respect
Hope this will help you.sql
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER
-----
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E
STOCKDETAILS
-----------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
--------------
FOR OFFICE - 1
--------------
ITEMCODE ITEMNAME OFFICEID
--------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
Thanks in advanceWelcome to the Forum.
Check this...
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = '1') AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1|||Hi Rudra !
how to get such all result in view and then i will query for officeid to get my desired output.|||Hi r937 !
I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?|||Hi r937 !
I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?
Why don't you use stored proc and pass parameter using OFFICEID ? I think that would be a better way to deal with your problem.But I am not sure what your requirement is...|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1
hmm,always ahead...;)|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?
if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?
my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?
if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?
my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?
I suggest you to use stored proc,its always good to use stored proc
in your case.Just write this...
CREATE PROCEDURE dbo.StockView(
@.officeid VARCHAR(20)
AS
--Use mine or Rudy's one
--this is mine
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = @.officeid) AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME
--OR use Rudy's one
select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = @.officeid
Go
And check BOL to use Stored proc in dataset .Its very easy man and better to use in many respect
Hope this will help you.sql
It's slow to use ServerXMLHTTP to submit a query to SQLXML virtual directory
I'm using ServerXMLHTTP object in an ASP page (web server) to submit a query to our database server (SQL Server 2000) via SQLXML virtual directory. The ServerXMLHTTP object will return me more than 5000 rows in XML format which is about 15M in size.
The problem is, it takes 1 minute for the ServerXMLHTTP object to get the response from SQLXML web service. That makes our web application not workable because it's really slow.
However, if I use XMLHTTP object instead of ServerXMLHTTP object, it only takes seconds to finish the same query. I know these 2 objects are implemented in different ways. XMLHTTP is designed for client applications and relies on URLMon, which is built upon Microsoft Win32 Internet (WinInet). ServerXMLHTTP is designed for server applications and relies on a new HTTP client stack, WinHTTP. ServerXMLHTTP offers reliability and security and is server-safe. So I'd better use ServerXMLHTTP in my web application if I know how to solve the speed issue.
Can somebody help me out? Thank you very much in advance. This problem happens recently. The program had been working for 3 years.
The url opened by ServerXMLHTTP object is like http://myserver/myvd?sql=select * from staff where gender='M' and staff_id<5000 for xml auto&root=Root
Here are some things to look at.
ServerXMLHTTP can have proxy issues. Check if the time is spent making the connection. [more]
It can take 15+ seconds to negotiate certificates. Are certificates involved? If so, are you reusing the ServerXMLHTTP object? If so does the delay occurs only on the first use of the ServerXMLHTTP object?
If it's possible in your scenario (not enough info for me to tell), you can use ServerXMLHTTP in asynchronous mode to increase concurrency in your app (work while you wait).
HTH!
Tim
sqlIT WORKED!
Hello everyone,
i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.
Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?
thanks a lot.
Have you tried using Data Conversion transform...
you could use an expression like
IsNull(Column) ? ValueifNull : ValueifNotNull
See some examples here:
http://msdn2.microsoft.com/en-us/library/ms141184.aspx
|||Thanks a lot, the code and the article helped a lot.I used an Derived Column Transformation to parse null values to zeros with the expression. The good thing is that i don't loose the mapping to the old columns. that's great.
Additionally this article helps to understand Derived Column Transformation:
http://msdn2.microsoft.com/en-us/library/ms141069.aspx
regards amigo|||
So, you got it!
That's great.
Wednesday, March 21, 2012
Issues with performance of XQuery on SQL Server 2005
Hi folks,
we are executing the following Xquery on SQLserver 2005.
select
policy_xml.query('/Policy/PolicyApplication/Inuserer/InsurerID'),
policy_xml.query('/Policy/PolicyApplication/Insurer/AccountIdentifier'),
policy_xml.query('/Policy/PolicyApplication/Insurer/Type'),
policy_xml.query('/Policy/PolicyApplication/Insurer/HolderName')
from policyTable
where
policy_xml.exist('/Policy/PolicyApplication/Insurer/PolicyOwner/EntityID[.="E_1"]') = 1
Its taking 50 Secs to search from 10000 records{without indexes}
The table has 3 columns sno,policy_id,policy_xml.
We have primary index on policy_id field and 1 secondary index(path index) on the table.
When we enable the index the query takes 380 secs.
The size of loan_xml column is about 110 Kb for each row. We need to keep the indexes for some more complex update Xqueries. Is there a way out to improve the performance of the XQuery we are using? Please let us also know the reasons of decrease in performance using indexes on the table. Do indexs have any issues related to XQuery performance?
We have an urgent requirement to resolve this issue. Kindly let us know the resolution ASAP.
Please let us know if you require any other information in this regard.
Thanks,
Bhuvanesh
I am not a XML guru to exactly know where the problem might be but want to pass the following link
which talks of some performance techiniques.
http://msdn2.microsoft.com/en-us/library/ms345118(SQL.90).aspx
Regards
AK
|||Hi Bhuvanesh,
From what you said here:
The table has 3 columns sno,policy_id,policy_xml.
We have primary index on policy_id field and 1 secondary index(path index) on the table.
I assume you haven't create XML index on column policy_xml, the DDL statement will look like this
Code Snippet
createprimaryxmlindex p_xml_idx
on policyTable(policy_xml)
go
It should definitely improve your query performance. Please let me know if otherwise.
|||I've similar issue.
Table1 ( Xid , XML_data (xml)) where Xid is primary key and XML_data is of xml datatype.
I've set primary index on XML_data along with the three secondary indexes.
with fillfactor 90, padindex on and sort _in_tempdb is on.
xml_data stores 1000 xml's of size 150 kb each. i'm fetching single xml for particular value and it is taking 20 minutes on server with 2 gb ram.
the query is
SELECT
xml_data.query('/Info/PersonalInfo/Entity[1]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/Sno/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/Sno/text()')
FROM Table1
Looking out for valuable help.....
|||Try following query to see if any improvement. I change query() to value() since you seems want to get scalar value, not xml.
Code Snippet
SELECT
x.value('(.[1]/LastName)[1]','varchar(100)'),
x.value('(.[1]/FirstName)[1]','varchar(100)'),
x.value('(.[1]/Sno)[1]','varchar(100)'),
x.value('(.[2]/LastName)[1]','varchar(100)'),
x.value('(.[2]/FirstName)[1]','varchar(100)'),
x.value('(.[2]/Sno)[1]','varchar(100)')
FROM Table1 crossapply xml_data.nodes('/Info/PersonalInfo/Entity')as t(x)
Monday, March 12, 2012
Issue with SqlUserDefinedAggregate
I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries) that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
w.Write(myList.Count);
}
}
? If you want to maintain the list properly you should serialize/deserialize the list itself -- and not its count -- in the Read and Write methods. They may be called more than one during the course of aggregation. Your current code is probably failing because it's making assumptions about how and when these will be called. You should instead do something like: public void Write(BinaryWriter w) { w.Write(myList.Count); foreach (string theString in myList) w.Write(theString); } public void Read(BinaryReader r) { this.myList = new List<string>(); int numStrings = r.ReadInt32(); for (int i = 0; i<numStrings; i++) myList.Add(r.ReadString()); } ... Then, in your Terminate method: public SqlInt32 Terminate() { return new SqlInt32(myList.Count); } -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <FernandoT@.discussions..microsoft.com> wrote in message news:e4178255-6dd9-4b48-b5ae-eb072c88e314_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge". I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation. See below a simplified version of my code (posted just after the queries) that replicates the issue. The query below works because it does't process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund = 2 group by GroupID The query below doesn't work because it process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund <= 20 group by GroupID [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = 1000)] public class AggregIssue : IBinarySerialize { private List<string> myList; private int myResult; public void Init() { myList = new List<string>(); } public void Accumulate(SqlString Value) { if (Value.IsNull) { return; } myList.Add(Value.ToString()); } public void Merge(AggregIssue Other) { if (Other.myList != null) { if (myList == null) { myList = Other.myList; } else { myList.AddRange(Other.myList); } } } public SqlInt32 Terminate() { return new SqlInt32(myResult); } public void Read(BinaryReader r) { myResult = r.ReadInt32(); } //The code below is simplified for posting in the forum. //I do additional manipulation of the list and require //the aggregation to be IBinarySerialize. //But this code replicates the issue also public void Write(BinaryWriter w) { w.Write(myList.Count); } }|||
Hi Adam,
Thank you very much for your response.
The reason that I don't serialize the list itself is because of the MaxByteSize limitation of 8096. My list will easily go beyond that limitation. I test your solution and it works for a small list, but not for long ones.
I noticed that the list is loosing the information in the merge. If the query doesn't go through parallel threads, it works fine.
My assumption is that the serialization will happen before terminate and final output of aggregate value for each row of the result set.
Any other ideas?
Thanks!!
Fernando
|||Hi, Fernando,
I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.
About Merge() stuff, to my limited understanding:
If single thread (no parallel op):
Init() -> Accumulate() -> Terminate()
If multi threads (parallel plan):
T1: Init() -> Accumulate()
T2: Init() -> Accumulate() -> Merge( with T1)
T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()
This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.
In Adam's book Chapter 6, p. 195, I quote:
"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"
I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?
Regards,
Dong Xie
|||? Actually, that quote from the book is not quite accurate anymore -- when I wrote it against an earlier CTP it appeared to be true, but now it does not. I believe that it has been changed in a later printing of the book, but I'll check today with Apress to make sure. Thanks for pointing it out! Anyway, the correct phrasing at this point should be "the intermediate result can be serialized and deserialized up to one time per row of aggregated data" In other words, the SQL Server engine may not do it for every row (or even nearly that often in most cases), but you need to code your aggregate as if it will. There is not necessarily a direct/stated relationship between Merge and Read/Write, but it appears that when Merge is called the engine also does a round of serialization/deserialization. I'm not sure why, though. Hopefully Steven Hemingray or one of the other dataworks guys will show up in this thread and clarify! -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Dong Xie@.discussions.microsoft.com> wrote in message news:c5fafeee-262b-43a4-9295-10a7cb07d92a@.discussions.microsoft.com...Hi, Fernando,I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.About Merge() stuff, to my limited understanding:If single thread (no parallel op):Init() -> Accumulate() -> Terminate()If multi threads (parallel plan):T1: Init() -> Accumulate()T2: Init() -> Accumulate() -> Merge( with T1)T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.In Adam's book Chapter 6, p. 195, I quote:"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?Regards,Dong Xie|||
It seems that you are right and serialization/deserialization happens when merge is called.
For now I have a workaround that was suggested in another forum to use "MAXDOP=1" and it works as merge is never executed.
It is not ideal, as parallelism cannot be leveraged, but it is a workaround.
Thanks to all for the responses!!
Fernando
|||There seems to be a few issues within the thread:
1) Why is the private field myList nullified?
Whenever serialization takes place (Read/Write), a new instance is instantiated and it is up to your serialization code to fill the instance. Since your Write() only sets myResult, myList will be remain on the default value of null.
2) Why is MaxByteSize not always enforced?
MaxByteSize is enforced during serialization. You could cause instances to become much larger than 8k over Accumulate() calls and then not serialize out 8k.
With this said, there should not be a reason to build UDAggs that become larger than 8k but do not serialize out 8k. Read/write should serialize all necessary information for the UDAggs.
The UDAgg code within this thread is a good example of a contrived case for this. Since Terminate() only returns the count, there is not a need to accumulate the actual strings, but Accumulate() could increment a counter instead.
3) When is Read/Write called?
Within SQL Server 2005, serialization takes place during Merge() and before Terminate() is called. If the UDAgg provided accessed the myList within Terminate(), you would see a NullReferenceException from within Terminate() (since Write() does not reconstruct myList and null is its default reference).
As pointed out within this thread, Read/Write is not called for every row. However, please write your UDAgg with proper serialization semantics as if it were called every row.
Hope that helps!
-Jason
|||Jason,
Thanks for all your explanation. It makes sense to me now how all this work.
But I still have one issue, which is the limitation of 8K. The list that I am building cannot be contrived until it is complete just before "Terminate". In the example I am passing the count and I could do that in the merge also. But in my real case, I need the complete list to be used in the resolution of a non-linear equation, so I cannot do anything with it until I pass it to a iterative algorithm in order to solve the equation. But this list can grow bigger than 8K.
The only way to avoid so far is to limit the degree of parallelism to 1, so the code doesn't go to merge.
Any thoughts on this?
Thanks,
Fernando
|||Fernando-
Merge is also called in some more complex queries (such as GROUP BY WITH CUBE).
This is an interesting workaround to the 8k limitation for your scenario. I do worry that your workaround leaves your UDAgg prone to internal changes within SQL Server causes serialization to be more frequently performed or eliminated. For this reason, I'd recommend always using an approach where instances created from Read/Write are no different than the original.
I cannot recommend using this approach for these reasons, but limiting DOP to 1 should eliminate Merge() calls for the most part. If you absolutely must use this workaround, I'd recommend safeguarding against your assumptions (Merge never called, serialization takes place once after all Accumulate calls but before Terminate):
Always throw an exception within your Merge() call so that in the case that Merge() is invoked, you'll know what the issue is and you can try to simplify the query so that Merge is not called.|||Jason,
Thanks for all your help and explanations.
Fernando
Issue with SqlCeParameter (SqlCE 3.1)
Hi,
I have a simple query as follows:
SELECT COUNT(ID) FROM AI_DTREE DT WHERE PARENT =@.pPID AND
CARTRIDGE_ID = @.pCID AND COMMAND =@.pCMD AND OBJECT =@.pObj
Where @.pPID=16700130,@.pCID=43000000,@.pCMD=”=”, and
@.pObj=”the cecum, identified by appendiceal orifice & IC valve”
The filed OBJECT in AI_DTREE is of nvarchar(30)(of course the length of @.pObj is more than 30 in my current query).
I have build the SqlCeCommand sccmd object with the above sql text and the parameters.
returnval = sccmd.ExecuteScalar();
When I execute the above statement I am getting the following error:
ex.Message = "@.pObj : String truncation: max=30, len=55, value='the cecum, identified by appendiceal orifice & IC valve'."
But when I execute the same in Sql Server Management Studio against SqlCE db, it works fine and the result returnval =0.
How to overcome this SqlCeParameter issue?, for me it is difficult to messure the length of the filed before I exeuting the command.
Thanks
G Sreenaiah
Hi Erik,
Thanks for your prompt reply.
I am just executing the above command from my C# code. If I want to ensure the length of the parameter is less than the length of field, then I should go for one more database hit to fetch field’s length first.
This is not the way happening with i) OleDbCommand, OleDbParameter, ii) OracleParameter, OracleCommand
Please let me know if anybody else has an idea how this can be resolved with workout having one more db hit to fetch fields’ length.
Why it is happening only with SqlCeCommand, SqlCeParameter? why not with OracleParameter, OracleCommand ?
Thanks
G Sreenaiah
Issue with sorting reports
Hi,
I'm having an issue with sorting reports. I have an order by in the query which returns the results how I want them, but if I use the auto generated columns and layout they are not in the order that the query returns them. However, if I manually create a new table and put the results in that they are ordered fine.
I am having this problem in reporting services for sql 2000, but my workmate has had exactly the same thing in his reporting for 2005.
Any help would be greatly appreciated.
Rob
What is the auto generated columns and layout?|||How can you auto generate columns in sql reporting table? Are you using Visibility expression for the columns in the table to make it look dynamic? Can you provide the query and the report layout you have along with the explanation of the problem?
Shyam
Issue with SELECT "IN" statement
In Reporting Services I have following query. For some reason query only
picks the FIRST choice for State or Product â?¦ even though user selects
multiple products or states â?¦ what is the issue here? Could you please help
me?
Thank you.
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (@.StateId)On Jun 4, 10:43 am, ozcan <o...@.discussions.microsoft.com> wrote:
> In Reporting Services I have following query. For some reason query only
> picks the FIRST choice for State or Product ... even though user selects
> multiple products or states ... what is the issue here? Could you please help
> me?
> Thank you.
> SELECT
> DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
> YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
> s.ts_name
> FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
> WHERE
> (transitionlabel <> 'Update' OR transitionlabel <> '')
> AND ts_productsubsystem IN (@.ProductId)
> AND ts_newstate IN (@.StateId)
You will most likely need to loop through the multi-select report
parameter and insert the values in a temp table (in a query/stored
procedure outside the report) and then use the above query to access
the values in the temp table. Here is a query that should get you
started in looping through the multi-select report parameter values
selected.
--CREATE PROC ParseRSMultiParameterList
DECLARE
@.STATES VARCHAR(MAX)
--AS
DECLARE @.STATEBUFFER VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.STATES = 'AL,TN,CA,OH';
--TEST DATA--
CREATE TABLE #STATELIST (State char(2));
SET @.STATEBUFFER = @.STATES;
WHILE (LEN(@.STATEBUFFER) > 0)
BEGIN
IF (CHARINDEX(',', @.STATEBUFFER) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.STATEBUFFER) = 0)
BEGIN
SET @.END_POSITION = LEN(@.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION + 1)));
END
SET @.STATEBUFFER = RIGHT(@.STATEBUFFER, (LEN(@.STATEBUFFER) -
@.END_POSITION));
END
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (select State from #STATELIST)
DROP TABLE #STATELIST;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You may want to look at the Report, Parameters setting (from layout view) and
see if that parameter has the "multi-value" checkbox selected. If it is not
checked, I would think that the end user would only be able to select one
single parameter, and this does not seem to be the issue - but may want to
check it.
picks the FIRST choice for State or Product â?¦ even though user selects
multiple products or states â?¦ what is the issue here? Could you please help
me?
Thank you.
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (@.StateId)On Jun 4, 10:43 am, ozcan <o...@.discussions.microsoft.com> wrote:
> In Reporting Services I have following query. For some reason query only
> picks the FIRST choice for State or Product ... even though user selects
> multiple products or states ... what is the issue here? Could you please help
> me?
> Thank you.
> SELECT
> DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
> YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
> s.ts_name
> FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
> WHERE
> (transitionlabel <> 'Update' OR transitionlabel <> '')
> AND ts_productsubsystem IN (@.ProductId)
> AND ts_newstate IN (@.StateId)
You will most likely need to loop through the multi-select report
parameter and insert the values in a temp table (in a query/stored
procedure outside the report) and then use the above query to access
the values in the temp table. Here is a query that should get you
started in looping through the multi-select report parameter values
selected.
--CREATE PROC ParseRSMultiParameterList
DECLARE
@.STATES VARCHAR(MAX)
--AS
DECLARE @.STATEBUFFER VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.STATES = 'AL,TN,CA,OH';
--TEST DATA--
CREATE TABLE #STATELIST (State char(2));
SET @.STATEBUFFER = @.STATES;
WHILE (LEN(@.STATEBUFFER) > 0)
BEGIN
IF (CHARINDEX(',', @.STATEBUFFER) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.STATEBUFFER) = 0)
BEGIN
SET @.END_POSITION = LEN(@.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION + 1)));
END
SET @.STATEBUFFER = RIGHT(@.STATEBUFFER, (LEN(@.STATEBUFFER) -
@.END_POSITION));
END
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (select State from #STATELIST)
DROP TABLE #STATELIST;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You may want to look at the Report, Parameters setting (from layout view) and
see if that parameter has the "multi-value" checkbox selected. If it is not
checked, I would think that the end user would only be able to select one
single parameter, and this does not seem to be the issue - but may want to
check it.
Wednesday, March 7, 2012
Issue with full-text query (containstable)..
I've got a full-text index on a table 'items' with an image field
'item' into which I've imported word and pdf documents. I've only got
~10 docs imported currently, for testing. I know that each of these
documents, except for one, includes the term 'ontology' or
'ontologies'.
I've got the following query, which gives back only two of these
documents (1 word, 1 pdf):
select i.*, ct.rank as incl_ctr
from items i inner join
containstable(items, item,
' ("class*" and "info*") or "ontology" '
) ct on i.id = ct.[key]
where (ct.rank > 1) and i.incl_ext in ('.doc','.pdf')
order by ct.rank desc;
I know this is incomplete, based on the content of each document, which
is an issue I'd like to work out; but prior to that, if I write the
query with "onto*" as a prefix term instead of "ontology", it only
gives back one of the two docs (the pdf):
select i.*, ct.rank as incl_ctr
from items i inner join
containstable(items, item,
' ("class*" and "info*") or "onto*" '
) ct on i.id = ct.[key]
where (ct.rank > 1) and i.incl_ext in ('.doc','.pdf')
order by ct.rank desc;
I can't identify why this is. I'd be very interested in whatever input
I could get on this..
Installed evaluation version: 2000 - 8.00.194 on Windows XP.
Installing the pdf ifilter improves the circumstance described in the
initial post; yet I think it's odd that onto* yields fewer docs in that
query than ontology, which continues to be the case..
'item' into which I've imported word and pdf documents. I've only got
~10 docs imported currently, for testing. I know that each of these
documents, except for one, includes the term 'ontology' or
'ontologies'.
I've got the following query, which gives back only two of these
documents (1 word, 1 pdf):
select i.*, ct.rank as incl_ctr
from items i inner join
containstable(items, item,
' ("class*" and "info*") or "ontology" '
) ct on i.id = ct.[key]
where (ct.rank > 1) and i.incl_ext in ('.doc','.pdf')
order by ct.rank desc;
I know this is incomplete, based on the content of each document, which
is an issue I'd like to work out; but prior to that, if I write the
query with "onto*" as a prefix term instead of "ontology", it only
gives back one of the two docs (the pdf):
select i.*, ct.rank as incl_ctr
from items i inner join
containstable(items, item,
' ("class*" and "info*") or "onto*" '
) ct on i.id = ct.[key]
where (ct.rank > 1) and i.incl_ext in ('.doc','.pdf')
order by ct.rank desc;
I can't identify why this is. I'd be very interested in whatever input
I could get on this..
Installed evaluation version: 2000 - 8.00.194 on Windows XP.
Installing the pdf ifilter improves the circumstance described in the
initial post; yet I think it's odd that onto* yields fewer docs in that
query than ontology, which continues to be the case..
Issue with CONTAINSTABLE statement
I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
table for the value 'Meets':
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
Any ideas?
Thanks in advance.GAH.. Had not run a start_full for the index. All is working now.
Thanks!
Nate wrote:
Quote:
Originally Posted by
I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
>
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
>
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
>
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
>
Any ideas?
>
Thanks in advance.
Subscribe to:
Posts (Atom)