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

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.
Feb
12
comment SQL as a Storage Definition Language (SDL)
The SDL stuff is almost anything that's not specified by the SQL standard that is different in every DBMS and that specifies anything to do with how or where the data in the relevant table is stored. So 'yes: SET storage_engine=MYISAM is a MySQL-specific example of SDL'.
Feb
8
answered Transaction in SQL Server
Feb
8
comment Transaction in SQL Server
Which DBMS are you using? (Given the [dbo].[SP3] notation, it's likely to be MS SQL Server, but it's as well to be specific.) Are autonomous transactions a possibility?