Reputation
434
Top tag
Next privilege 500 Rep.
Access review queues
Badges
4 11
Newest
 Caucus
Impact
~10k people reached

Jul
28
comment Informix performing slow - Query is around joining 10+ tables and spanning 70 lines
Informix does not yet have CTEs. You're going to need to identify which version of Informix you are using (e.g. 7.31.UD5 or 12.10.FC4) and the platform (O/S) on which it is running. When you say 'queries spanning 70 lines', is that 70 lines of SQL? How big are the tables — size (bytes) of a row, number of rows, number of columns? Which indexes do you have? Which primary and foreign keys? What's the query? What's the query plan (from SET EXPLAIN)? What disks do you have? There's a mass of data needed to be able to start helping you — quite possibly too much for a question on DBA.SE.
Jul
28
answered SSRS and Informix connection
Jul
27
awarded  Caucus
May
4
comment Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)
That's going to take a lot of digesting. There are over 700 lines in the query plan information. The SQL alone is about 50 lines. It ain't happening tonight — that is, I'm off to bed and won't be working on this until tomorrow evening at the earliest. Are your statistics up to date? These days, it is more nearly automatic than it was, say, 5 years ago. Seeing SKIP 10000 FIRST 50 is worrying; there've been 200 sets of 50 rows prior to that? How many rows are there in the full data set? The estimate is 11k or so, if I scanned the query correctly, but what's the actual number?
May
3
revised Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)
Provide link to Redis; unquote NoSQL since it really isn't code.
May
3
comment Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)
I agree with @zgguy; there isn't enough information in the question to be able to provide a meaningful answer. To provide a better answer, we'd need to see outline schemas for the tables (the columns mentioned in the query, the size of the other columns, the number of rows in each table, the indexes on each table), and the query itself. Studying the query plan (output from SET EXPLAIN ON) will provide insight into what the query is doing. Do the parameters of the query change each time it is used? Do the SKIP and FIRST clauses make it worse? How many rows in the result set, and how wide?
May
3
suggested approved edit on Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)
May
3
revised Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)
improve title and other trivia.
May
3
suggested approved edit on Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)
May
3
awarded  Informed
Mar
20
answered Informix Server Rejected Connection
Jan
20
answered Informix: is it necessary to update statistics on views?
Jul
28
revised How to join the latest previous record with SQL
Use correct () instead of {} in CREATE TABLE statement; indent body of statement
Jul
28
suggested approved edit on How to join the latest previous record with SQL
Sep
9
comment Better Indexing for MySQL?
As with all the best questions — it depends. You need unique constraints (indexes) on the combinations of columns for your candidate keys (one of which is your primary key; it may be the sole candidate key). You don't need a separate single column index on the leading column of the unique constraint indexes. Whether you need indexes on other columns depends on the queries you're going to be running. If they can be answered well with the candidate key indexes, the extras are just getting in the way. If there are frequently run queries which will benefit, then add extra indexes. Measure!
Aug
20
awarded  Yearling
May
20
awarded  Constituent
May
14
answered Can't Select a View on Informix
May
13
awarded  Caucus
Apr
11
comment SQL Index order and performance based on cardinality and data
It looks like a slam dunk for an index on B(Id1, Id2) or B(Id1, Id2, Type_Code) to improve performance. At least this index would support the join condition more simply, leaving the type_code check to the end, instead of forcing it at the beginning. The COALESCE may prevent the optimizer using the index and thus speed things up, paradoxically.