ERD & SQL: The most horrbile !

delacruz · 2011-02-01 21:55:08

I am the lucky individual who has the work with the following

One SQL query

Last edited by delacruz (2011-02-01 22:08:43)

schuay · 2011-02-01 22:01:40

I don't want to sound condescending, but that's a rather small database and not a particularly large query
Also, are you sure this isn't sensitive / confidential information (even if names are blanked out)?

Last edited by schuay (2011-02-01 22:04:14)

delacruz · 2011-02-01 22:10:21

schuay wrote:

I don't want to sound condescending, but that's a rather small database and not a particularly large query
Also, are you sure this isn't sensitive / confidential information (even if names are blanked out)?

You are right i took off the query.

There is 25 other pages that goes with the above diagram. The problem with the query is that it takes like 50 seconds to shoot out like 30 tuples. It repeats a lot of data over and over.

schuay · 2011-02-01 22:45:23

SQL does have the tendency to get messy (at least visually) as the codebase grows. I'd much rather write SQL than use SSIS though

Often, there's also huge potential for optimization - correct table indices, storing subresults in indexed temporary tables, join hints if necessary. 50 seconds does seem a bit much, unless your server is running on a netbook or the table has > 50 million rows..

Looking at the query you posted, it seems a bit weird to use both DISTINCT while doing a GROUP BY over the same columns (DISTINCT is slower than GROUP BY). Judging from the column names, the ones ending in _num look like ints. Why are you doing LTRIM(RTRIM()) on those? Why use any functions / string concatenation at all in the GROUP BY clause (bypassing any indices you might have) instead of grouping by the actual columns? The subquery (again using DISTINCT) on vCurrentTimesheet also look unnecessary to me.

Have you checked execution plans? These should display precisely where performance bottlenecks are located.

Of course, if the views that are being used in these queries are already slow, optimizing this part won't help alot. And yes, it certainly looks like many (if not all) of these statements could be merged into a single one

Last edited by schuay (2011-02-01 23:06:15)

delacruz · 2011-02-01 23:00:38

schuay wrote:

SQL does have the tendency to get messy (at least visually) as the codebase grows. I'd much rather write SQL than use SSIS though
Often, there's also huge potential for optimization - correct table indices, storing subresults in indexed temporary tables, join hints if necessary..
50 seconds does seem a bit much, unless your server is running on a netbook or the table has > 50 million rows..

The database is ten years old and is not optimized at ALL!!! To make matters worst it was made by a chemical engineer. Hey if you can develope software and not be a CS then go for it. But if you are making garbage, I just dont know what the original "developer" was thinking.

tavianator · 2011-02-01 23:46:43

I hate to be a one-upper, but until last year one of import scripts ran an SQL query that was about 250 lines long, had about 20 CASE WHEN statements to work around crappy foreign data, and was written as the UNION of two giant SELECT clauses that JOINed on about 20 tables each. I kept a copy of the query to remind myself to never let my code look like that. Our database's schema diagram, printed in about the same font size as yours, covers my boss's wall. Oh, and our codebase has nearly a million lines of C# and PHP.

So don't complain, someone's always got it worse than you . I know someone must have it worse than me.

Last edited by tavianator (2011-02-01 23:47:10)

delacruz · 2011-02-02 00:18:19

tavianator wrote:

million lines of C# and PHP.

This is very rare. Most programs are no where near one million lines. In fact software engineering technics break down around 2 and above million lines.

tavianator · 2011-02-02 04:42:47

delacruz wrote:

tavianator wrote:
million lines of C# and PHP.
This is very rare. Most programs are no where near one million lines. In fact software engineering technics break down around 2 and above million lines.

Tell that to the kernel devs

However, I should've said "nearly half a million," because I just totalled the line counts for all source files on the whole project and it's 453,302 SLOC.

Xyne · 2011-02-02 05:26:29

Pah, that's nothing.

I once had to work with a bajillion lines of COBOL and obfuscated Perl written by a half-blind street vendor. The code retrieved data from a database using a query string that was longer than the human genome, and the schema diagram, printed in about the same font size as yours, depleted the rain forest and covered the entire Sahara.

Arch Linux

#1 2011-02-01 21:55:08

ERD & SQL: The most horrbile !

#2 2011-02-01 22:01:40

Re: ERD & SQL: The most horrbile !

#3 2011-02-01 22:10:21

Re: ERD & SQL: The most horrbile !

#4 2011-02-01 22:45:23

Re: ERD & SQL: The most horrbile !

#5 2011-02-01 23:00:38

Re: ERD & SQL: The most horrbile !

#6 2011-02-01 23:46:43

Re: ERD & SQL: The most horrbile !

#7 2011-02-02 00:18:19

Re: ERD & SQL: The most horrbile !

#8 2011-02-02 04:42:47

Re: ERD & SQL: The most horrbile !

#9 2011-02-02 05:26:29

Re: ERD & SQL: The most horrbile !

Board footer