running 70+ queries in one go

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 432 Views - Last Post: 27 June 2018 - 08:22 AM

#1 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

running 70+ queries in one go

Posted 25 June 2018 - 07:52 AM

((select (round((((select count(a.prod_no) from prod_result a where a.prod_type = 'FIN' and 
                a.fac_code = 'M001' and a.prod_order_no = '" + textBox1.Text + "' and a.grade = 'A') - 
                (select count(a.prod_no) from prod_result a, rework_history d 
                where a.fac_code = 'M001' and a.prod_type = 'FIN' and 
                a.result_id = d.result_id and a.grade = 'A' and
                a.prod_order_no = '" + textBox1.Text + "')) / (select count(a.prod_no) 
                from prod_result a where a.prod_type = 'FIN' and 
                a.fac_code = 'M001' and prod_order_no = '" + textBox1.Text + "')), 4)) * 100
                from prod_result a, rework_history d 
                where a.fac_code = 'M001' and a.prod_type = 'FIN' and a.grade = 'A' and
                a.prod_order_no = '" + textBox1.Text + "' group by 1) || '%') as Pass_Rate



This is sample sql code I put to find pass_rate on specific product. When I type in product order # on textbox on c# program, it finds passing rate of that product. The problem is, my boss is asking me to make a program that displays all the information in one go when he enters product code and in order to do that, I have to run similar query for each column which is total 70+ columns. Each product has 500-3000 in qty so this is kind of massive calculation if I repeat similar query 70 times in a one go. It took about 5-8 mins to get a result on c# program. Is there any way to make this go faster?

Is This A Good Question/Topic? 0
  • +

Replies To: running 70+ queries in one go

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 25 June 2018 - 07:55 AM

I need some background information..

What is 'pass rate' in this case?
Why is what ever you are doing for 'pass rate' happening in 70 other columns?
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 25 June 2018 - 08:11 AM

Good gravy, Miss Daisy... that is ugly.

First up - use parameters. Definitively do not.. DO NOT.. be appending in straight what ever from a textbox.

Bad; use parameters.
...
and a.prod_order_no = '" + textBox1.Text + "' 
...
a.fac_code = 'M001' 
and a.prod_type = 'FIN' 
...



Do your tables all have appropriate keys? If so you should be using joins when you can.

Example:

This:
select count(a.prod_no) 
from prod_result a with(nolock)
join rework_history d with(nolock) on a.result_id = d.result_id 
where a.fac_code = 'M001' 
	and a.prod_type = 'FIN' 					
	and a.grade = 'A' 
	and a.prod_order_no = '" + textBox1.Text + "')

instead of this:
(select count(a.prod_no) 
from prod_result a
, rework_history d 
where a.fac_code = 'M001' 
	and a.prod_type = 'FIN' 
	and a.result_id = d.result_id 
	and a.grade = 'A' 
	and a.prod_order_no = '" + textBox1.Text + "')





Trying to make the original query less ugly and more readable.
(
(select 
		(round(
			(
				(
					(select count(a.prod_no) 
					from prod_result a 
					where a.prod_type = 'FIN' 
						and a.fac_code = 'M001' 
						and a.prod_order_no = '" + textBox1.Text + "' 
						and a.grade = 'A'
					) - 
					(select count(a.prod_no) 
					from prod_result a
					, rework_history d 
					where a.fac_code = 'M001' 
						and a.prod_type = 'FIN' 
						and a.result_id = d.result_id 
						and a.grade = 'A' 
						and a.prod_order_no = '" + textBox1.Text + "')
				) / 
					(select count(a.prod_no) 
					from prod_result a 
					where a.prod_type = 'FIN' 
						and a.fac_code = 'M001' 
						and prod_order_no = '" + textBox1.Text + "')
			)
				, 4)
			)
						 * 100
					from prod_result a
					, rework_history d 
					where a.fac_code = 'M001' 
					and a.prod_type = 'FIN' 
					and a.grade = 'A' 
					and a.prod_order_no = '" + textBox1.Text + "' 
					group by 1
) || '%'
) as Pass_Rate


Also it looks like you are repeating the same query for the same four parameters.. why not dump that into a temp table and use that instead of the whole DB all over again. Yeah and it shouldn't affect the join you will be adding.
Was This Post Helpful? 0
  • +
  • -

#4 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: running 70+ queries in one go

Posted 25 June 2018 - 08:25 AM

View Postmodi123_1, on 25 June 2018 - 07:55 AM, said:

I need some background information..

What is 'pass rate' in this case?
Why is what ever you are doing for 'pass rate' happening in 70 other columns?


pass rate is total # of A grade product without extra rework / total product #.

product #s are changing each time.

So

query 1: total A grade product
query 2: total A grade product that has rework history
query 3: total product #.

so pass rate is basically total A grade product with no rework history divided by total product #. Rework history table has no other connection to product result table other than prod_result id. -0-;;
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 25 June 2018 - 08:33 AM

Fair enough..
1. Parameters.
2. Proper joins.
3. Temp table usage.

.. though I am still unclear on this "70 other columns" comments.
Was This Post Helpful? 0
  • +
  • -

#6 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: running 70+ queries in one go

Posted 25 June 2018 - 08:34 AM

I will try parameters and temp table usage.. :(/>

70 other columns are basically product information and other rate.
about 40 of them are # of defect codes on first repair work or wasted product's defect code #s by defect code names. There are about 20 different defect code names.
All these columns require count(something) from product # or get a percentage of something using those counted #s.

This post has been edited by shin777: 25 June 2018 - 08:38 AM

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 25 June 2018 - 08:35 AM

... AND joins.

Also what is the datatype of "prod_order_no"?
Was This Post Helpful? 0
  • +
  • -

#8 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: running 70+ queries in one go

Posted 25 June 2018 - 08:44 AM

View Postmodi123_1, on 25 June 2018 - 08:35 AM, said:

... AND joins.

Also what is the datatype of "prod_order_no"?


it's varchar2(30). It's mixture of alphabet and numbers.

This post has been edited by shin777: 25 June 2018 - 08:44 AM

Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 25 June 2018 - 09:00 AM

Is that the primary key column?
Was This Post Helpful? 0
  • +
  • -

#10 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7397
  • View blog
  • Posts: 15,329
  • Joined: 16-October 07

Re: running 70+ queries in one go

Posted 25 June 2018 - 09:20 AM

I saw that modi123_1 played the unraveling game already, but I wanted to play myself to get a feel:
(
  (
    select (
      round(
        (
          (
            (
              select count(a.prod_no)
                from prod_result a 
                  where a.prod_type = 'FIN'
                    and a.fac_code = 'M001'
                    and a.prod_order_no = '" + textBox1.Text + "' 
                    and a.grade = 'A'
            ) - (
              select count(a.prod_no) 
                from prod_result a, rework_history d 
                where a.fac_code = 'M001'
                  and a.prod_type = 'FIN'
                  and a.result_id = d.result_id 
                  and a.grade = 'A' 
                  and a.prod_order_no = '" + textBox1.Text + "'
            )
          ) / (
            select count(a.prod_no) 
              from prod_result a 
                where a.prod_type = 'FIN'
                  and a.fac_code = 'M001'
                  and prod_order_no = '" + textBox1.Text + "'
          )), 4)
    ) * 100
      from prod_result a, rework_history d 
      where a.fac_code = 'M001'
        and a.prod_type = 'FIN'
        and a.grade = 'A'
        and a.prod_order_no = '" + textBox1.Text + "' group by 1
) || '%') as Pass_Rate



Just saw that grade A explanation. That's interesting.

Let's think all product counts first.

select prod_order_no, prod_type, fac_code, count(prod_no) as all_count
  from prod_result
  group by prod_order_no, prod_type, fac_code



Now, the grade A thing. Consider:
select prod_order_no, prod_type, fac_code, 
    sum(case when grade='A' then 1 else 0 end) as grade_a_count,
    count(prod_no) as all_count
  from prod_result
  group by prod_order_no, prod_type, fac_code



That sum only counts the A's, so we needn't do the same aggregate twice.

Given this, I think you may be looking for something like:
select a.prod_order_no, a.prod_type, a.fac_code, a.grade, 
    round(((a.grade_a_count - IFNULL(b.hist_count, 0)) / a.all_count), 4) * 100 || '%' as Pass_Rate
  from (
    select prod_order_no, prod_type, fac_code, 
        sum(case when grade='A' then 1 else 0 end) as grade_a_count,
        count(.prod_no) as all_count
      from prod_result
      group by prod_order_no, prod_type, fac_code
    ) a
      left join (
        select a.prod_order_no, a.prod_type, a.fac_code, count(a.prod_no) as hist_count
          from prod_result a
            join rework_history d on a.result_id = d.result_id 
          where  a.grade = 'A'
          group by a.prod_order_no, a.prod_type, a.fac_code
        ) b on a.prod_order_no=b.prod_order_no
          and a.prod_type=b.prod_type
          and a.fac_code=b.fac_code
  where a.fac_code = 'M001'
    and a.prod_type = 'FIN'
    and a.prod_order_no = ?



Removing that last WHERE will get you, well, all the products with the counts. Of note is the MySQL IFNULL to allow for a lack of history. The left join also allows for that lack.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#11 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: running 70+ queries in one go

Posted 26 June 2018 - 06:10 PM

how do you use parameters again? -_-;;

like.. string := prod_order_no
to replace text box?
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 26 June 2018 - 06:16 PM

What is your server side language?
Was This Post Helpful? 0
  • +
  • -

#13 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: running 70+ queries in one go

Posted 26 June 2018 - 06:20 PM

oracle sql. i am using oracle sql developer and visual studio 2017 windows form. o.O
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: running 70+ queries in one go

Posted 27 June 2018 - 12:39 AM

..... So is that vb.net? C#?
Was This Post Helpful? 0
  • +
  • -

#15 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7397
  • View blog
  • Posts: 15,329
  • Joined: 16-October 07

Re: running 70+ queries in one go

Posted 27 June 2018 - 05:50 AM

The windows forms bit is irrelevant ( albeit a poor choice for a new project .)

For Oracle drivers, the "where id = :id" format is favored. Here's an ancient blog endorsed by Oracle, but it honestly hasn't changed that much. And, well, since you're using ancient GUI paradigms, probably not an issue: https://blogs.oracle...alues-that-bind
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2