Database

11-Mar-08 7:28

It's been a while since I worked with this, but I remember having the same issue. I believe adding a relation adds both a check constraint and a foreign key constraint. You should be able to loop through the constraints collection and remove the check constraints and leave the foreign key constraints in place.

Re: DataRelation and NOCHECK add constraint

CJacobus12-Mar-08 11:15

CJacobus

12-Mar-08 11:15

Thanks for the reply.

Any idea how I distinguish them? I expected a Type property or something like that on the Constraint object. Or, checking the constraint type (i.e., ForeignKeyConstraint)...

Charles.

Re: DataRelation and NOCHECK add constraint

Mark J. Miller13-Mar-08 3:29

13-Mar-08 3:29

You can use GetType() and check the type of the object. If you're using C# you could use :

if(constraint is ForeignKeyConstraint)

How can i improve the performance of this query?

tonyong11-Mar-08 4:24

Re: How can i improve the performance of this query?

11-Mar-08 4:24

Hi
am quite new to SQL/ADO.

how can i improve the speed of retrieving data of this query?
I tried very long already but still..
thats is the best i can get.
it took 10s to complete the whole query.
Its querying oracle database.

SELECT DISTINCT ppar_engparameters.parmval, ppar_engparameters.parmname, prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatis
FROM prcd, ppar_engparameters
WHERE ppar_engparameters.parmval='" & name & "' AND prcd.activeflag='A'
AND ppar_engparameters.prcdname = prcd.prcdname

and also how can i combine this query to the first one without compromising on the speed of retrieving data?
when i tried, it took me hundreds of seconds.

SELECT catnumber, category
FROM catg_pll
where catg_pll.partprcdname||' '||catg_pll.partprcdversion = prcd.prcdname||' '||prcd.prcdversion

Scott Dorman11-Mar-08 4:42

Scott Dorman

11-Mar-08 4:42

Some things to look at are:

Make sure you have proper indexes on your tables.
Look at using some inner (or outer) joins, depending on what data you are trying to return.

Scott.

—In just two days, tomorrow will be yesterday.
—Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai

[Forum Guidelines] [Articles] [Blog]

Re: How can i improve the performance of this query?

tonyong11-Mar-08 4:59

Re: How can i improve the performance of this query?

11-Mar-08 4:59

hi,

I am not sure of the indexes on the tables as this is a company database and I am actually a student on attachment and the company ask me to try this.
I had tried using inner joins but the speed got even slower result return.
Thus I am now lost of what to do already. Haix. Cry | :((

Scott Dorman11-Mar-08 5:07

Scott Dorman

11-Mar-08 5:07

tonyong wrote:
I am not sure of the indexes on the tables as this is a company database and I am actually a student on attachment and the company ask me to try this

Honestly that shouldn't matter. You are working for them for a reason. It should be expected that you ask some level of questions in order to get your job done. However, given the situation you may need to ask those questions a bit more carefully than normal. If you ask something along the lines of "You want to verify the indexes on the tables" I don't see any reason this should cause problems.

You may want to review the joins again. My guess is that you used the wrong type of join. It's been a long time since I've worked with Oracle, but if there is any type of tool that lets you look at the execution plan for the query (the steps that the database engine will perform to actually run the query) that is the best place to start. That should tell you if indexes are being used, how many rows are being returned for each part of the query, etc.

Scott.

—In just two days, tomorrow will be yesterday.
—Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai

[Forum Guidelines] [Articles] [Blog]

Re: How can i improve the performance of this query?

tonyong11-Mar-08 5:28

Re: How can i improve the performance of this query?

11-Mar-08 5:28

I see. I will go and check again with them.

I am not sure which type of joins to use as my knowledge with regards to sql is very little. In school we are given the codes to see the results and this is actually the first time i am trying to come up with my own codes to query the database and for days i am still not able to come up with any better than then first one.

There is 3 tables i need to get data from. All the 3 table has 2 common keys.

As for the execution plan, I guess i had to ask them to see if i am able to get them.

Pete O'Hanlon11-Mar-08 12:23

Pete O'Hanlon

11-Mar-08 12:23

This[^] article, and this[^] one might help.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Re: How can i improve the performance of this query?

tonyong12-Mar-08 0:34

Re: How can i improve the performance of this query?

12-Mar-08 0:34

Hi,

after reading the two, i came out with a new query that combines the two query together. However the speed is still not good. Still thinking of ways to make it faster.

In the query what i want is to get data out from 3 tables that is corresponded to B value in one of the table.

andyharman12-Mar-08 5:19

andyharman

12-Mar-08 5:19

The following SQL would return all of the data from all 3 tables:

SELECT ppar_engparameters.parmval, ppar_engparameters.parmname,
    prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag,
    prcd.prodstatis, catg_pll.catnumber, catg_pll.category
FROM ppar_engparameters
INNER JOIN prcd
  ON prcd.prcdname = ppar_engparameters.prcdname
  AND prcd.activeflag = 'A'
LEFT OUTER JOIN catg_pll
  ON catg_pll.partprcdname = prcd.prcdname
  AND catg_pll.partprcdversion  = prcd.prcdversion
WHERE ppar_engparameters.parmval= 'MyValue'

I have removed the "distinct" clause that you had in the original. This is often used to hide poorly-designed tables - you understand where-and-why you are getting duplicates before blindly using this.

Proper indexes are required to make this run fast. I would expect the following indexes (or primary keys) from your original description:

--For the link from product-parameter to product.
create unique index prcd_idx1 on prcd (prcdname, prcdversion)
--For the link from product.
create index catg_pll_idx1 on catg_pll (partprcdname, partprcdversion)
--For the search on parameter values.
create index ppar_engparameters_idx1 on ppar_engparameters
     (parmval, activeflag)

Other indexes may also be required for other activities. There should ideally be a primary key or unique-index on every table.

Oracle's query optimiser uses statistical data from indexes to determine the best way of answering your queries. There is a command (that I cannot remember) for recalculating these statistics.

Oracle is also able to display the query-plan that it uses - so you would be able to tell exactly what Oracle is doing under the covers. Search Google[^] for more information.

Hope that helps.

Regards
Andy

Re: How can i improve the performance of this query?

tonyong12-Mar-08 14:22

Re: How can i improve the performance of this query?

12-Mar-08 14:22

The distinct clause does make a difference of ard 20s. But how come it give repeated values?

My current code is

select distinct ppar_engparameters.parmval, ppar_engparameters.parmname,<br />
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatus, <br />
catg_pll.catgnumber, catg_pll.category<br />
from ppar_engparameters<br />
inner join prcd<br />
on prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A'<br />
inner join catg_pll<br />
on catg_pll.partprcdname = ppar_engparameters.prcdname and catg_pll.partprcdversion = <br />
ppar_engparameters.prcdversion<br />
where ppar_engparameters.parmval='MyValue'

As for the query plan and stuffs i still cant access to it.

Thanks.
Tony

tonyong13-Mar-08 2:28

Re: How can i improve the performance of this query?

13-Mar-08 2:28

Btw.. Is there anyway for ADO to return only certain column back into excel?

Mark J. Miller11-Mar-08 7:35

11-Mar-08 7:35

I've never used Oracle, so this may or may not help. But in SQL Server concatenation of strings will disable the use of any indexes. So your 2nd query might perform better if you rewrote it like this:

<br />
SELECT catnumber, category <br />
FROM catg_pll<br />
where catg_pll.partprcdname = prcd.prcdname AND catg_pll.partprcdversion = prcd.prcdversion <br />

SQL Reporting Services - Anyone an expert?

imnotso#11-Mar-08 3:50

11-Mar-08 3:50

Can I have tabbed reports ie (more than 1 report using on sp) on SQL reporting services 2000?

So it would look similar to more than 1 Excel spreadsheet.

Any help welcome...

Harvey

Mark J. Miller11-Mar-08 7:24

11-Mar-08 7:24

I'm working on a project which loads 8 reports at once. Each report queries the same data but just aggregates it differently. So what I had to do was 'preload' the data first, aggregate it and store the results (the aggregates not the actual data - too much) in a permenant table. Then I load the reports and they each just query the results table and display the reports (charts). The reason for this approach was that the query which was common to each report took at least 3 minutes to run and sometimes a lot more.

I haven't investigated what you're suggesting, and I'm using 2005. But I don't think it's possible as each report uses a separate connection and they don't load syncronously.

imnotso#11-Mar-08 23:51

11-Mar-08 23:51

Thanks Mark, but I'm just running one query, say on sales in a year, I then want the report to split into 12 tabbed reports, one for each month. Is this possible in SQL reporting Services 2000, I have been told someone has seen it once !!

Mark Churchill12-Mar-08 0:36

Mark Churchill

12-Mar-08 0:36

You can filter your dataset from your sproc after the fact for your table/matrix.

Mark Churchill
Director
Dunn & Churchill
Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.

imnotso#12-Mar-08 1:21

12-Mar-08 1:21

Hi,

Thanks for the help Mark,

Filter my dataset in reports designer you mean?

Harvey

Mark Churchill12-Mar-08 2:18

Mark Churchill

12-Mar-08 2:18

Yep, on the tabled data region you should be able to set filters. Fine for smaller datasets.

If the dataset you are getting back from your sproc is large, I'd probably lean towards filtering it server side and getting one dataset for each month...

Mark Churchill
Director
Dunn & Churchill
Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.

imnotso#12-Mar-08 2:33