Max Query: running

Showing posts with label running. Show all posts

Wednesday, March 28, 2012

Help with SPs

Hi there...I'm noticing my pages are running a little slow and I'm
wondering if two SPs I have on every page are the culprits; any way we
can make these more efficient? There are three tables involved:
Zipc: this contains all the zip codes, latitudes and longitudes in the
US
Addr: this contains a user's mailing address
Adve: this contains an advertiser's mailing address
I am pulling content based on a site visitor's zip code (regardless of
whether they're a user or an advertiser), so I need to go into the Zipc
table and pull the Latitude and Longitude so I can determine a radius.
First, I get the latitude and longitude:
CREATE PROCEDURE getlocal
@.user nvarchar(30)
AS
select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
Uniq=@.user)
GO
'in other words, I'm telling the database to get me the latitude and
longitude from the Zipc table where the zip code in that table is equal
to the zip code in the Addr table where the User (or advertiser) is the
one viewing the site
Then, after getting the latitude and longitude and establishing the
outermost reaches of the radius, I pull the five advertisements closest
to the user in no particular order:
CREATE PROCEDURE getmorelocal
@.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
decimal(18,9)
AS
select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
ORDER BY newid()
GO
'in other words, give me five advertisements where their latitudes and
longitudes are contained within the parameters.
Any advice would be appreciated; thanks.Maybe this will help?
http://databases.aspfaq.com/database/how-do-i-create-a-store-locator-feature.html
"Erik Lautier" <lautier@.gmail.com> wrote in message
news:1168881086.981248.69840@.51g2000cwl.googlegroups.com...
> Hi there...I'm noticing my pages are running a little slow and I'm
> wondering if two SPs I have on every page are the culprits; any way we
> can make these more efficient? There are three tables involved:
> Zipc: this contains all the zip codes, latitudes and longitudes in the
> US
> Addr: this contains a user's mailing address
> Adve: this contains an advertiser's mailing address
> I am pulling content based on a site visitor's zip code (regardless of
> whether they're a user or an advertiser), so I need to go into the Zipc
> table and pull the Latitude and Longitude so I can determine a radius.
> First, I get the latitude and longitude:
> CREATE PROCEDURE getlocal
> @.user nvarchar(30)
> AS
> select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
> Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
> Uniq=@.user)
> GO
> 'in other words, I'm telling the database to get me the latitude and
> longitude from the Zipc table where the zip code in that table is equal
> to the zip code in the Addr table where the User (or advertiser) is the
> one viewing the site
> Then, after getting the latitude and longitude and establishing the
> outermost reaches of the radius, I pull the five advertisements closest
> to the user in no particular order:
> CREATE PROCEDURE getmorelocal
> @.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
> decimal(18,9)
> AS
> select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
> Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
> BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
> WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
> ORDER BY newid()
> GO
> 'in other words, give me five advertisements where their latitudes and
> longitudes are contained within the parameters.
> Any advice would be appreciated; thanks.
>|||Aaron,
Thanks. I actually don't need to get too specific with the distances,
so trig functions are a little more than is necessary right now. What
concerns me more is to have the best syntax for the SPs I listed...any
advice on that? For example, rather than having a compound statement
in the first SP, would it be better to break it down into two SPs where
I get the Zip in the first one and then the lats and longs in the
second one? Or, conversely, would it be better to wrap *everything* up
into just one SP?
Erik
Aaron Bertrand [SQL Server MVP] wrote:
> Maybe this will help?
> http://databases.aspfaq.com/database/how-do-i-create-a-store-locator-feature.html
>
>
> "Erik Lautier" <lautier@.gmail.com> wrote in message
> news:1168881086.981248.69840@.51g2000cwl.googlegroups.com...
> > Hi there...I'm noticing my pages are running a little slow and I'm
> > wondering if two SPs I have on every page are the culprits; any way we
> > can make these more efficient? There are three tables involved:
> >
> > Zipc: this contains all the zip codes, latitudes and longitudes in the
> > US
> > Addr: this contains a user's mailing address
> > Adve: this contains an advertiser's mailing address
> >
> > I am pulling content based on a site visitor's zip code (regardless of
> > whether they're a user or an advertiser), so I need to go into the Zipc
> > table and pull the Latitude and Longitude so I can determine a radius.
> >
> > First, I get the latitude and longitude:
> >
> > CREATE PROCEDURE getlocal
> > @.user nvarchar(30)
> > AS
> > select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
> > Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
> > Uniq=@.user)
> > GO
> > 'in other words, I'm telling the database to get me the latitude and
> > longitude from the Zipc table where the zip code in that table is equal
> > to the zip code in the Addr table where the User (or advertiser) is the
> > one viewing the site
> >
> > Then, after getting the latitude and longitude and establishing the
> > outermost reaches of the radius, I pull the five advertisements closest
> > to the user in no particular order:
> >
> > CREATE PROCEDURE getmorelocal
> > @.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
> > decimal(18,9)
> > AS
> > select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
> > Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
> > BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
> > WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
> > ORDER BY newid()
> > GO
> > 'in other words, give me five advertisements where their latitudes and
> > longitudes are contained within the parameters.
> >
> > Any advice would be appreciated; thanks.
> >|||On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
wrote:
>Thanks. I actually don't need to get too specific with the distances,
>so trig functions are a little more than is necessary right now. What
>concerns me more is to have the best syntax for the SPs I listed...any
>advice on that? For example, rather than having a compound statement
>in the first SP, would it be better to break it down into two SPs where
>I get the Zip in the first one and then the lats and longs in the
>second one? Or, conversely, would it be better to wrap *everything* up
>into just one SP?
The sub-selects return small sets, can't see any style problems.
J.|||Ok...having removed those two SPs on a couple test pages, it's apparent
that they are definintely the culprits...perhaps I've just got a slow
SQL Server on my host?
JXStern wrote:
> On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
> wrote:
> >Thanks. I actually don't need to get too specific with the distances,
> >so trig functions are a little more than is necessary right now. What
> >concerns me more is to have the best syntax for the SPs I listed...any
> >advice on that? For example, rather than having a compound statement
> >in the first SP, would it be better to break it down into two SPs where
> >I get the Zip in the first one and then the lats and longs in the
> >second one? Or, conversely, would it be better to wrap *everything* up
> >into just one SP?
> The sub-selects return small sets, can't see any style problems.
> J.|||On 15 Jan 2007 14:16:51 -0800, "Erik Lautier" <lautier@.gmail.com>
wrote:
>Ok...having removed those two SPs on a couple test pages, it's apparent
>that they are definintely the culprits...perhaps I've just got a slow
>SQL Server on my host?
Do you have the proper indexes for the subs to run against? How to
they run on their own?
J.
>JXStern wrote:
>> On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
>> wrote:
>> >Thanks. I actually don't need to get too specific with the distances,
>> >so trig functions are a little more than is necessary right now. What
>> >concerns me more is to have the best syntax for the SPs I listed...any
>> >advice on that? For example, rather than having a compound statement
>> >in the first SP, would it be better to break it down into two SPs where
>> >I get the Zip in the first one and then the lats and longs in the
>> >second one? Or, conversely, would it be better to wrap *everything* up
>> >into just one SP?
>> The sub-selects return small sets, can't see any style problems.
>> J.

Monday, March 26, 2012

HELP with SP_configure

I am running the following command:

exec sp_configure 'allow updates', '0'

I get:

Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.

What's causeing this?

Thanks!

Kenyou need to be associated (at least) with serveradmin role to be able to perform this operation. check with your sql server dba on that.

Friday, March 23, 2012

Help with security model for RS implementation needed

We're running Reporting Services (wSP1) on a Win 2003 server box. Presently
(for development), SQL Server, the web application and RS all run on the
same box. I've configured an app pool in IIS under which Reports,
ReportServer and the web application run. I'm also collecting credentials
via forms auth which I pass as the credentials to RS during web service
calls. We are using URL access to access rendered reports.
RS Windows Service is configured to run as NT AUTH\Network Service.
All datasources are set up using trusted security.
What I'd like to be able to do to ensure that we use connection pooling is
not impersonate the credentials passed in but instead connect to the OLAP
database as a single domain account.
Is this possible and if so, what security configuration changes should I
make to make this happen?
Thanks in advance.
-TimPlease disregard my original post. The absurd amounts of caffeine I've been
consuming lately have caused temporary memory loss. :)
-Tim
"Tim Ellison" <TimEllison@.direcway.com> wrote in message
news:Oajl$kstEHA.1400@.TK2MSFTNGP11.phx.gbl...
> We're running Reporting Services (wSP1) on a Win 2003 server box.
Presently
> (for development), SQL Server, the web application and RS all run on the
> same box. I've configured an app pool in IIS under which Reports,
> ReportServer and the web application run. I'm also collecting credentials
> via forms auth which I pass as the credentials to RS during web service
> calls. We are using URL access to access rendered reports.
> RS Windows Service is configured to run as NT AUTH\Network Service.
> All datasources are set up using trusted security.
> What I'd like to be able to do to ensure that we use connection pooling is
> not impersonate the credentials passed in but instead connect to the OLAP
> database as a single domain account.
> Is this possible and if so, what security configuration changes should I
> make to make this happen?
> Thanks in advance.
> -Tim
>

HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Wednesday, March 21, 2012

Help with returning too much data

I am running the below query and getting back results that have the word
"mode" in it. Isn't the keyword CONTAINS supposed to treat my search
expression as one word? Can someone show me what is wrong with this query so
that it returns only records that have the exact search expression "mode-4"
in it? Thank you.
SELECT MyFields
FROM MyTable M
LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
indexed or are they integer values?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>I am running the below query and getting back results that have the word
> "mode" in it. Isn't the keyword CONTAINS supposed to treat my search
> expression as one word? Can someone show me what is wrong with this query
> so
> that it returns only records that have the exact search expression
> "mode-4"
> in it? Thank you.
> SELECT MyFields
> FROM MyTable M
> LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
> LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
> LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
> LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
|||Not sure what you mean by "are they integer values", but the table that
contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
full-text indexed? Course, I'm thinking yes since you asked the question

"Hilary Cotter" wrote:

> are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
> indexed or are they integer values?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>
>
|||Perhaps if you could post the schema. For the record mode-4 is indexed and
queried two separate words. If 4 is not in your noise word list this should
work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...[vbcol=seagreen]
> Not sure what you mean by "are they integer values", but the table that
> contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
> full-text indexed? Course, I'm thinking yes since you asked the question
>

> "Hilary Cotter" wrote:
|||Sorry, but our company specifically prohibits posting any schema details in
newsgroups, but should the order be:
1. Remove words from the noise list.
2. Create the full-text index.
I'm wondering that since I created the index before removing the 4 from the
noise list, that it may be the reason my search is not working.
"Hilary Cotter" wrote:

> Perhaps if you could post the schema. For the record mode-4 is indexed and
> queried two separate words. If 4 is not in your noise word list this should
> work.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...
>
>
|||OK, let me guess your schema then from what you have posted.
Create MyFields(PK int not null identity primary key, Fields1 char(20),
Field2 char(20), Field3 char(20), Field4 char(20))
Create T1 (pk int not null references MyFields(PK), Field1 char(20))
Create T2 (pk int not null references MyFields(PK), Field2 char(20))
Create T3 (pk int not null references MyFields(PK), Field3 char(20))
Create T4 (pk int not null references MyFields(PK), Field4 char(20))
This is kind of critical as I think your join condition is all wrong.
But you are correct with a search on mode-4 and you have removed 4 from your
noise word list after building your index you will not get correct results.
In fact you should get fewer results which makes me wonder about your join
condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...[vbcol=seagreen]
> Sorry, but our company specifically prohibits posting any schema details
> in
> newsgroups, but should the order be:
> 1. Remove words from the noise list.
> 2. Create the full-text index.
> I'm wondering that since I created the index before removing the 4 from
> the
> noise list, that it may be the reason my search is not working.
> "Hilary Cotter" wrote:
|||The join condition is there because there are many other items that we are
building a where clause on. I simplified the query to what I thought was most
necessary and only included the joins to be true to my actual query, but with
what you have said about creating the full-text after removing the number 4
from the noise file, now I do not think they matter. If I could show you the
actual schema, I think you would agree with me.
Thank you very much for your time. I'll recreate the full-text index, after
removing any noise words, and see how it works for me then.
"Hilary Cotter" wrote:

> OK, let me guess your schema then from what you have posted.
> Create MyFields(PK int not null identity primary key, Fields1 char(20),
> Field2 char(20), Field3 char(20), Field4 char(20))
> Create T1 (pk int not null references MyFields(PK), Field1 char(20))
> Create T2 (pk int not null references MyFields(PK), Field2 char(20))
> Create T3 (pk int not null references MyFields(PK), Field3 char(20))
> Create T4 (pk int not null references MyFields(PK), Field4 char(20))
> This is kind of critical as I think your join condition is all wrong.
> But you are correct with a search on mode-4 and you have removed 4 from your
> noise word list after building your index you will not get correct results.
> In fact you should get fewer results which makes me wonder about your join
> condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...
>
>
|||MS changed the worbreaker in windows 2003, to contain what I consider
is now a bug.
The hyphen in 'mode-4' is actually now used to split the phrase into 2
words, therefore doing an OR search, hence returning every record with
'mode' OR '4' in it, which I expect will be quite a few.
On windows 2000, this worked properly to join the word, as a hyphen is
actually supposed to in text.
Eventually I got around it by replacing all hyphens in the indexed
text with HYP ie 'modeHYP4'. You need to replace any hyphens that
users enter in the search to the same.
Lots of our product skus had hyphens in so it was causing all sorts of
problems, 21-500 was returning thousands of results instead of 1.
Hope this helps...
On 14 Feb, 19:35, Mike Collins <MikeColl...@.discussions.microsoft.com>
wrote:
> The join condition is there because there are many other items that we are
> building a where clause on. I simplified the query to what I thought was most
> necessary and only included the joins to be true to my actual query, but with
> what you have said about creating the full-text after removing the number 4
> from the noise file, now I do not think they matter. If I could show you the
> actual schema, I think you would agree with me.

Help With Report That Shows Late Sales Orders

Hello Everyone,
I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
reports is coming from a SQL 2000 EE database. All running on Server 2003.
I've been asked to create a report that shows all sales orders that are past
thier due date. I'm having some difficulty in coming up with a way to do this.
The table I'm pulling the information from is named oe_hdr (Order Entry
Header)
In the table there is a field named req_date (required date). This field is
used by the sales staff to enter in the date that the order is due to ship
from our facility. There is also a field named complete. This field is
checked when the order is invoiced, I think.
The sales staff wants a report that shows all orders that have not shipped
by thier due date.
Based on the info I've provided does anyone have an idea how I can make this
report. Maybe an expression or something. If this is not enough info, please
let me know.
Thanks.On May 7, 11:50 am, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Hello Everyone,
> I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> reports is coming from a SQL 2000 EE database. All running on Server 2003.
> I've been asked to create a report that shows all sales orders that are past
> thier due date. I'm having some difficulty in coming up with a way to do this.
> The table I'm pulling the information from is named oe_hdr (Order Entry
> Header)
> In the table there is a field named req_date (required date). This field is
> used by the sales staff to enter in the date that the order is due to ship
> from our facility. There is also a field named complete. This field is
> checked when the order is invoiced, I think.
> The sales staff wants a report that shows all orders that have not shipped
> by thier due date.
> Based on the info I've provided does anyone have an idea how I can make this
> report. Maybe an expression or something. If this is not enough info, please
> let me know.
> Thanks.
So you want the report to ONLY show late orders and nothing else?
Put a where clause that includes this:
select *
from oe_hdr
where getdate() >= req_date and complete = ?
I'm not sure what data is put into that complete field, but whatever
indicated that is it NOT complete, then it should be put where I
placed the question marks. The script will look for any orders where
the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
req_date and the order has not completed - therefore it is overdue.
I hope this helps. A helpful tip, it might be better for your
company's privacy to make up names for tables. You never know how
people may use your information.|||Thanks Ayman,
Since writing my query, I found this in Visual Studio help;
=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
What this does is compare the value of the importantdate with todays date
and if the importandate is greater than a day old, it will format the date
font as red, otherwise blue. This gets me closer to what I'm looking for.
I'll massage this and see what i come up with. I will also give your
suggestion a go as well.
Thanks for the privacy info.
"Ayman" wrote:
> On May 7, 11:50 am, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Hello Everyone,
> > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > I've been asked to create a report that shows all sales orders that are past
> > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > The table I'm pulling the information from is named oe_hdr (Order Entry
> > Header)
> > In the table there is a field named req_date (required date). This field is
> > used by the sales staff to enter in the date that the order is due to ship
> > from our facility. There is also a field named complete. This field is
> > checked when the order is invoiced, I think.
> >
> > The sales staff wants a report that shows all orders that have not shipped
> > by thier due date.
> >
> > Based on the info I've provided does anyone have an idea how I can make this
> > report. Maybe an expression or something. If this is not enough info, please
> > let me know.
> > Thanks.
> So you want the report to ONLY show late orders and nothing else?
> Put a where clause that includes this:
> select *
> from oe_hdr
> where getdate() >= req_date and complete = ?
> I'm not sure what data is put into that complete field, but whatever
> indicated that is it NOT complete, then it should be put where I
> placed the question marks. The script will look for any orders where
> the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> req_date and the order has not completed - therefore it is overdue.
> I hope this helps. A helpful tip, it might be better for your
> company's privacy to make up names for tables. You never know how
> people may use your information.
>|||On May 7, 12:25 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Thanks Ayman,
> Since writing my query, I found this in Visual Studio help;
> =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> What this does is compare the value of the importantdate with todays date
> and if the importandate is greater than a day old, it will format the date
> font as red, otherwise blue. This gets me closer to what I'm looking for.
> I'll massage this and see what i come up with. I will also give your
> suggestion a go as well.
> Thanks for the privacy info.
> "Ayman" wrote:
> > On May 7, 11:50 am, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Hello Everyone,
> > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > I've been asked to create a report that shows all sales orders that are past
> > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > Header)
> > > In the table there is a field named req_date (required date). This field is
> > > used by the sales staff to enter in the date that the order is due to ship
> > > from our facility. There is also a field named complete. This field is
> > > checked when the order is invoiced, I think.
> > > The sales staff wants a report that shows all orders that have not shipped
> > > by thier due date.
> > > Based on the info I've provided does anyone have an idea how I can make this
> > > report. Maybe an expression or something. If this is not enough info, please
> > > let me know.
> > > Thanks.
> > So you want the report to ONLY show late orders and nothing else?
> > Put a where clause that includes this:
> > select *
> > from oe_hdr
> > where getdate() >= req_date and complete = ?
> > I'm not sure what data is put into that complete field, but whatever
> > indicated that is it NOT complete, then it should be put where I
> > placed the question marks. The script will look for any orders where
> > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > req_date and the order has not completed - therefore it is overdue.
> > I hope this helps. A helpful tip, it might be better for your
> > company's privacy to make up names for tables. You never know how
> > people may use your information.
Thats a good way to show all the data and filter it by color. Hint:
you can put nicer colors by using their numbers like "#dedab5" in
place of red or blue. If you select the entire data row, then put
that IIF command in the background color box under properties. It will
make the entire row change color as opposed to one cell - usually
easier to see.|||Ayman,
Do you have any suggestions on how to use the Datediff in my query to pull
the reports that are a day late?
Thanks.
"Ayman" wrote:
> On May 7, 12:25 pm, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Thanks Ayman,
> > Since writing my query, I found this in Visual Studio help;
> >
> > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> >
> > What this does is compare the value of the importantdate with todays date
> > and if the importandate is greater than a day old, it will format the date
> > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > I'll massage this and see what i come up with. I will also give your
> > suggestion a go as well.
> >
> > Thanks for the privacy info.
> >
> > "Ayman" wrote:
> > > On May 7, 11:50 am, Damon Johnson
> > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > Hello Everyone,
> > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > > > I've been asked to create a report that shows all sales orders that are past
> > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > Header)
> > > > In the table there is a field named req_date (required date). This field is
> > > > used by the sales staff to enter in the date that the order is due to ship
> > > > from our facility. There is also a field named complete. This field is
> > > > checked when the order is invoiced, I think.
> >
> > > > The sales staff wants a report that shows all orders that have not shipped
> > > > by thier due date.
> >
> > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > report. Maybe an expression or something. If this is not enough info, please
> > > > let me know.
> > > > Thanks.
> >
> > > So you want the report to ONLY show late orders and nothing else?
> > > Put a where clause that includes this:
> > > select *
> > > from oe_hdr
> > > where getdate() >= req_date and complete = ?
> >
> > > I'm not sure what data is put into that complete field, but whatever
> > > indicated that is it NOT complete, then it should be put where I
> > > placed the question marks. The script will look for any orders where
> > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > req_date and the order has not completed - therefore it is overdue.
> >
> > > I hope this helps. A helpful tip, it might be better for your
> > > company's privacy to make up names for tables. You never know how
> > > people may use your information.
> Thats a good way to show all the data and filter it by color. Hint:
> you can put nicer colors by using their numbers like "#dedab5" in
> place of red or blue. If you select the entire data row, then put
> that IIF command in the background color box under properties. It will
> make the entire row change color as opposed to one cell - usually
> easier to see.
>|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.sql

Monday, March 19, 2012

Help with query....

I have this query which I've left running for around 8 hours and does not
return (but is eating a lot of CPU and DISK IO).
The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
above 25% used, 1.1 disk queue. This server is not being used by anyone
else accept my query.
Integrations_activity has 25million rows, candidate has 1.4 million rows.
Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
using MAXDOP 1 to see if parallelism made a difference and it still ran for
8 hours before I killed it.
The query plan is shown below
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock)
join Candidates (nolock) ON candidates.indnum =
Integrations_Activity.IndNum
AND candidates.indofficenum =
Integrations_Activity.Indofficenum
WHERE Integrations_Activity.typeofactivity = 'CAN' AND
Integrations_Activity.IndNum IS NOT NULL
StmtText
----
----
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock), Candidates (nolock)
Where Integrations_Activity.IndNum IS NOT NULL
And Integrations_Activity.typeofactivity = 'CAN'
And Integrations_Activity.IndNum = Candidates.IndNum
And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
(1 row(s) affected)
StmtText
----
|--Table Update(OBJECT

[RMTEST].[dbo].[Integrations_Activity]),
SET

[Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Aggregate, HASH

[Bmk1000]),
RESIDUAL

[Bmk1000]=[Bmk1000])
DEFINE

[Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS

[Bmk1000]))
|--Hash Match(Inner Join,
HASH

[Candidates].[IndNum],
[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
RESIDUAL

[Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
[Expr1006]=[Candidates].[IndOfficeNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS

[Candidates].[IndNum], [Candidates].[IndOfficeNum]))
| |--Hash Match(Inner Join,
HASH

[Bmk1002])=([Bmk1002]), RESIDUAL

[Bmk1002]=[Bmk1002]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS

[Bmk1002]))
| | |--Index
Scan(OBJECT

[RMTEST].[dbo].[Candidates].[pk_Candidates]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS

[Bmk1002]))
| |--Index
Scan(OBJECT

[RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS

[Integrations_Activity].[IndNum], [Expr1006]))
|--Compute
Scalar(DEFINE

[Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
|--Table
Scan(OBJECT

[RMTEST].[dbo].[Integrations_Activity]),
WHERE

[Integrations_Activity].[IndNum]<>NULL AND
[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
(15 row(s) affected)
Answered in .programming. Please don't multi-post as we can't guess
everywhere where you posted the question.
Regards
Mike
"Paul" wrote:

> I have this query which I've left running for around 8 hours and does not
> return (but is eating a lot of CPU and DISK IO).
> The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
> 8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
> above 25% used, 1.1 disk queue. This server is not being used by anyone
> else accept my query.
> Integrations_activity has 25million rows, candidate has 1.4 million rows.
> Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
> using MAXDOP 1 to see if parallelism made a difference and it still ran for
> 8 hours before I killed it.
> The query plan is shown below
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum =
> Integrations_Activity.IndNum
> AND candidates.indofficenum =
> Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
> Integrations_Activity.IndNum IS NOT NULL
>
> StmtText
> ----
> ----
> ----
> ----
> ----
>
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock), Candidates (nolock)
> Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
> (1 row(s) affected)
> StmtText
> ----
> ----
> ----
> |--Table Update(OBJECT

[RMTEST].[dbo].[Integrations_Activity]),
> SET

[Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH

[Bmk1000]),
> RESIDUAL

[Bmk1000]=[Bmk1000])
> DEFINE

[Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS

[Bmk1000]))
> |--Hash Match(Inner Join,
> HASH

[Candidates].[IndNum],
> [Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
> RESIDUAL

[Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
> [Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS

[Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
> HASH

[Bmk1002])=([Bmk1002]), RESIDUAL

[Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS

[Bmk1002]))
> | | |--Index
> Scan(OBJECT

[RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS

[Bmk1002]))
> | |--Index
> Scan(OBJECT

[RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS

[Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
> Scalar(DEFINE

[Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
> Scan(OBJECT

[RMTEST].[dbo].[Integrations_Activity]),
> WHERE

[Integrations_Activity].[IndNum]<>NULL AND
> [Integrations_Activity].[typeofactivity]='CAN') ORDERED)
> (15 row(s) affected)
>
>
>
|||Can you post the CREATE TABLE AND CREATE INDEX statements, and the
non-parallel plan? Also, are there any constraints, indexed views, or
other dependent objects?
Do you have any idea how many rows this query will update?
Steve Kass
Drew University
Paul wrote:

>I have this query which I've left running for around 8 hours and does not
>return (but is eating a lot of CPU and DISK IO).
>The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
>8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
>above 25% used, 1.1 disk queue. This server is not being used by anyone
>else accept my query.
>Integrations_activity has 25million rows, candidate has 1.4 million rows.
>Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
>using MAXDOP 1 to see if parallelism made a difference and it still ran for
>8 hours before I killed it.
>The query plan is shown below
>Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum =
>Integrations_Activity.IndNum
> AND candidates.indofficenum =
>Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
>Integrations_Activity.IndNum IS NOT NULL
>
>StmtText
>----
>----
>----
>----
>----
>
>Update Integrations_Activity
>Set CandidateID = Candidates.CandidateID
>From Integrations_Activity (nolock), Candidates (nolock)
>Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
>(1 row(s) affected)
>StmtText
>----
>----
>----
>---
> |--Table Update(OBJECT

[RMTEST].[dbo].[Integrations_Activity]),
>SET

[Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH

[Bmk1000]),
>RESIDUAL

[Bmk1000]=[Bmk1000])
>DEFINE

[Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
>COLUMNS

[Bmk1000]))
> |--Hash Match(Inner Join,
>HASH

[Candidates].[IndNum],
>[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
>RESIDUAL

[Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
>[Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS

[Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
>HASH

[Bmk1002])=([Bmk1002]), RESIDUAL

[Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS

[Bmk1002]))
> | | |--Index
>Scan(OBJECT

[RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS

[Bmk1002]))
> | |--Index
>Scan(OBJECT

[RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS

[Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
>Scalar(DEFINE

[Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
>Scan(OBJECT

[RMTEST].[dbo].[Integrations_Activity]),
>WHERE

[Integrations_Activity].[IndNum]<>NULL AND
>[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
>(15 row(s) affected)
>
>
>

Wednesday, March 28, 2012

Help with SPs

Help with SPs

Help with SPs

Monday, March 26, 2012

HELP with SP_configure

Friday, March 23, 2012

Help with security model for RS implementation needed

HELP with Running value totals

Wednesday, March 21, 2012

Help with returning too much data

Help With Report That Shows Late Sales Orders

Monday, March 19, 2012

Help with query....

Max Query

Blog Archive

About Me