3 Answers Every Performance Tuning Experts Always Seeking
Sybase Performance Tuning Interview Questions
Reporting: SQL Performance and Tuning
This is a list of some techniques used successfully at several different sites.
1) Verify tables have had “update statistics” on them ;
Verify tables have had “sp_recompile” on them.
2) Verify any declared variables have the same data
type as their corresponding columns – this is a common
3) Force index usage as follows, with a hint:
4) Use SET TABLE COUNT
Example: set table count 6
Then, compile the procedure, in the same session.
5) If temp tables are being used, put the temp table
creation statements in one procedure, and the
processing SQL in another procedure. This allows
the optimizer to form a query plan on the already
Example:
– Plan for growth. Assume the driver table doubled or tripled in size; would
the report still function ?
– Avoid dumb comparisons in the where clause, like
– use “WHERE EXISTS ( )” rather than “WHERE NOT EXISTS”
– use “!=” rather than “<>”
– use “IS NOT NULL” rather than “<>NULL”
– use “IS NULL” rather than “=NULL”
– avoid distinct if possible ; see cursor loop option below
– use meaningful names for temp tables … don’t use #temp (lame)
Single query reports are rare – usually they involve getting a simple list
– Don’t try to ‘shoehorn’ SQL into one statement. Shorter programs are
great for C or Perl applications, but this is not the case in SQL.
Think “Bigger is Better” (and more maintainable).
– Keep queries from using more than four tables if possible.
2) Cursor on driver table(s), with IF..THEN processing in loop
Using a cursor for complex reports almost always increases performance
when large tables and a lot of joins are involved.
– Keep cursor queries from using more than two tables if possible,
make sure this query performs well on its own.
– Try to have a unique key of some sort available within the tables involved.
Strange results have been known to occur when a cursor is scanning
rows that are exactly alike.
– Don’t use cursors for updating.
– Use IF statements for filtering results even further. In most cases:
A code construct like the one below is better than cramming the
logic in a where clause.
3) Set processing without cursors
This technique should be attempted when even a cursor construct fails to
Basically, the driver query is re-run with each iteration of the loop.
Sample, with cursor:
fetch cursor1 into @emp_id, @last_name, @salary
fetch cursor1 into @emp_id, @last_name, @salary
Sample, with set processing:
select @emp_id = 0, @loop = 1
Transaction Log Filling Up ?
If the transaction log is filling up, for tempdb or the main database, there
is likely something wrong with the report logic.
Things to check:
– Instead of repetitively updating each row, can the values be obtained
ahead of time, and then inserted with a single transaction ?
– Are the “joined” updates occuring on each row once ? When updating
using a join statement, make sure that the tables in question
are joined in a way that avoids duplicate rows. Try running the
SQL statement as a SELECT – check it out.
– Are you cramming 500,000 rows from a temp table into a db table ?
Try elminating the temp table.
– Create indexes on updated/inserted tables after the fact.
– Use “set rowcount” along with “waitfor delay” if log problems persist
*** A proper DBA will never extend the log segment, based on the needs of a
PILOT INTERVIEW Questions And Answers! (How to PASS an Airline Pilot Interview)
1. What is CRM process as Airline Pilot? The systematic and effective use of all available resources to ensure a desirable outcome and completion of the flight. Focusing on communication, teamwork, task allocation, decision-making and situational awareness. The major contributors in the CRM process are: Dispatchers, flight attendants, maintenance, ATC and of course the cockpit crew.
2. What is the MAP on a non-precision approach? Non-precision: MAP is usually visually identified by a capitol and bold “M” in the Jepps or at the end of the solid line on NOS. There are several ways to id the MAP: Published DME at the MAP, Navaid such as the middle marker (MM), or timing from the FAF
3. What is the importance of proficiency in several languages for a pilot? A private pilot may limit himself to knowledge of English good enough to understand control towers. Other pilots, being more publicly involved figures by necessity, greatly benefit from knowing more than one language.
4. What is your Proudest accomplishment? Becoming a pilot and graduating from college as to be the biggest accomplish so far.
5. What is the 1-2-3 rule? Requirements for filing an alternate. 1-2-3: 1hr before to 1hr after estimated time of arrival, the weather must be forecast to be 2000′ ceiling and 3SM vis.
6. What makes a good captain or what do you expect from a Captain? one that is knowledgeable, decisive, conservative and safety conscious, good people skills, superior communication skills, solid piloting skills, can manage stress and avoid confrontation
7. List your qualifications as a pilot? Besides relevant college degree(s), pilots graduate from flying school and participate in annual additional training.
8. What is GRID MORA? It’s an altitude derived by Jepp or provided by state authorities. If derived by Jepp, Grid MORA clear all terrain and structures by 1000′ in areas where highest elevations are 5000′ MSL or lower. MORA values clear all terrain and structures by 2000′ in areas where highest elevations are 5001′ MSL or higher. Grid MORA (State) altitude supplied by the State Authority provides 2000′ clearance in mountainous areas and 1000′ in non-mountainous areas. Also… a +/- denote doubtful accuracy but are believed to provide sufficient reference point clearance.
9. What is the definition of CRM? CRM is a system which allows optimum use of all available resources – equipment, procedures, interpersonal communication, team work – all to promote safety and enhance efficiency in operations
10. What is Vso of your most recent aircraft? Cessna 172 is 41Kts
11. What is the max distance from the runway if you were to circle to land? Depends on your speed
12. Have you applied anywhere else? Just be honest on this one. If you are in the job hunt, it’s normal to apply to several companies. It’s great to tell them that Cathay is your top choice, but it would be suspect to say Cathay is your only choice.
13. What is mach tuck? As the aircraft reaches critical mach the nose of the aircraft tends to pitch downwards due to the flow of air over the wing reaching supersonic speeds producing additional life and center of pressure moving rearward. As the center of pressure moves rearward the elevators become less effective, pushing the aircraft into a potentially unrecoverable dive.
14. What is tower’s NORDO signal to be cleared to land? Steady green
15. What is VMCG? minimum speed control in the ground
16. What is the difference between Blue, Green Airports? blue is IFR (has approaches) green is VFR (no approaches)
17. What is GRADIENT CLIMB? 3%
18. What is VMCA? Minimun Speed Control in the Air
19. What is windshear? its a change on direction and speed of the wind
20. What is MOCA? Minimum Obstacle Clearance that provides obstable clearance between Vor airways. It also ensures VOR coverage 22nm from the nearest VOR
21. What is hyperventilation? hyperventilation is when the body has too much Oxygen and not enough Co2
22. Ever had an emergency? yes, many over the years
23. Pilots have many simultaneous responsibilities during the flight. What can you tell me about this? Pilots navigate the flight, observe and direct the activities of the crew, check instruments, keep an eye on the weather, and monitor altitude and air traffic. Doing all these and more, often simultaneously, requires outstanding multi-tasking skills and power of concentration.
24. What are the max airspeeds in C airspace? 200 KIAS within 4 NM of Primary Class C Airport
25. How would you behave as the pilot of a plane during an emergency? Describe first what your emotional response would be. It is important to demonstrate, preferably through examples, that you can maintain your poise during such events, and that you possess enough presence of mind to activate emergency procedures and delegate duties among crew members as necessary.
26. Where do you see yourself in 6 months? with your company
27. Did you ever have to modify the flight course in the past? Pilots are able to modify the course in flight, if this is dictated by internal or external conditions. This is not a small responsibility and needs careful coordination with control towers and scrupulous attention to detail.
28. What Is V2? takeoff safety speed
29. What Is Max A/s In Class C? Airspeed maximum of 200 knots indicated within 4 nautical or 5 statute miles and 2500 feet above the ground of the primary airport.
30. What Are The Cdi/raim Sensitivities Of A Gps Outside 30nm? Inside 30nm? +- 5 mi +- 1 mi
31. What Is The Effect Of Aft Cg On Performance? rearward Cg gives better fuel efficiency
32. What Is An Lda? a precision approach that is offset from the runway, it may have GS
33. Where Is The Faf On A Vor/ils Approach? On a VOR approach, the FAP is whenever you are established on course inbound and the final decent may be started. For non-precision approaches, the FAF is the “Maltese Cross” and for precision, the arrow which identifies the GS intercept.
34. Why Cape Air? big airline paneer
35. What Kind Of Airspace Is Around Lax? Class B. Very congested airspace.
36. What Is V1? Take off decision speed
37. What is hypoxia? Lack of oxygen to body tissues
38. Why do you like Compass? I like the new modern equipment and I like the fact that it’s got flow through to/ is part of Northwest.
39. What is a squall line? It is a continuous narrow line active of thunderstorms ahead of cold front but can develop far removed of any front. May be too long to detour, too thick to penetrate. It forms very rapidly reach Max intensity in late afternoon or early evening. IT IS THE SINGLE MOST INTENSE WEATHER HAZARD FOR AIRCRAFT.
40. Why do you want to work for Allegiant? growing company, day trips, upgrade potential
41. Why Ameriflight? I’d like to fly cargo and gain the single-pilot IFR experience
42. Did you ever have a problem with an F/O? Yes
43. What is your clearance limit when cleared for the approach and then you loose coms? The published hold on the missed.
44. What is MEA? Minimum En Route Altitude, lowest altitude on that airway that assures obstacle clearance and acceptable navigational reception.
45. What is Definition of V2? Single engine climb speed until safe altitude or clear of obstacles
46. What is Definition of V1? on a balance runway it is the last chance for you to decide not to go if you have and emergency and still stop on the runway according to the air data
47. What is the definition of Hypoxia? Not enough Oxygen
48. What is the seating capacity of the Dash 8 100 and 300 and how many flight attendants you need? DA-100 (37) DA-300 (50) FA 1
49. Tell me the biggest factor as far as fuel conservation goes as it relates to a jet engine? TSFC thrust specific fuel consumption which directly inperpolates to power setting and altitude. Higher you climb the less fuel required. Throttle settings also have a say in conservation and relates more to maintenance issues which is why full throttle takeoffs are rarely done.
50. What qualities make a good captain? Confident, Diligent, Good CRM skills, Good Problem Solver
51. What’s your choice of base? PDX or Seattle
52. World Airways Pilot Interview Questions:
53. Flight Express Airline Pilot Interview Questions:
54. Lion Air Airline Pilot Interview Questions:
55. LAN Airline Pilot Interview Questions:
56. Miami Air International Airline Pilot Interview Questions:
57. Mokulele Airlines Pilot Interview Questions:
58. Ryan International Airlines Airline Pilot Interview Questions:
59. Seaborne Airlines Pilot Interview Questions:
60. XOJET Airline Pilot Interview Questions:
Transact SQL: numeric functions
Transact SQL: Finding duplicate rows in a table
This example finds cargo records with have duplicate destination ids.
8> where t2.dest_id = t1.dest_id ) > 1
Using Temporary Tables
Temp tables allow developers to create and scan tables within a stored procedure – and have the tables totally isolated from all other database connections. This is very valuable when results need to be processed several times within a loop, or when a complex result set is expected (like a crosstab). Note that temp table transactions are logged within tempdb (exception: select into create statements).
Temporary tables with names beginning with “#” that are created within stored procedures disappear when the procedure exits. A single procedure can:
Since the temporary table must exist in order to create procedures that reference it, here are the steps to follow:
You can create temporary tables without the # prefix, using create table tempdb..tablename from inside a stored procedure. These tables do not disappear when the procedure completes, so they can be referenced by independent procedures. Follow the steps above to create these tables.
Warning!
Create temporary tables with the “tempdb..” prefix from inside a stored procedure only if you intend to share the table among users and sessions. Stored procedures that create and drop a temporary table should use the # prefix to avoid inadvertent sharing.
General rules on temporary tables
Temporary tables with names that begin with # are subject to the following restrictions:
These restrictions do not apply to shareable, temporary tables created in tempdb.
Rules that apply to both types of temporary tables:
What is difference between SQL & T-SQL?
SQL- set of sqls are submitted individually to the database server.
T-SQL- the batch program is written where in all commands are submitted to the server in a single go. usually batches are run overnight and and all inserts and updates happen and these batches are scheduled. where as sqls’ are to run separately…..
all sqls’ are put in a file and schedule them called –t-sql .. besides it offers some other commands too.
SQL is the Structured Query Language the ANSI/ISO Standard database language. SQL Server’s implementation of the language is called Transact-SQL (T-SQL).
What is the difference between char and varchar data types?
char is used for fixed length memory storage whereas varchar
is used for variable lenght memory storage.
Fox Example if we have char(10) then we can store 10 bit
value in it but if we store only 6 bit value in it then rest
of the 4 bit space is goes wasted. this limitation is
In varchar if we use less space than define space the rest
of space is not wasted.
What is difference between User and Login in Sybase database ?
A server login is a name with which you connect to the ASE server from a client application. This requires a password. Login names and (encrypted) passwords are stored in master..syslogins. Once you’ve logged into the server, you still don’t have any right to access any databases. By default, when doing a ‘use your_db_name’,you’ll get an error that this login is not a user in that database. A database user is what controls access to each individual database for a specific login. So, in order to let a real-life user (i.e. a person) access a particular database, he/she requires (i) a login to connect to ASE, and (ii) a database user entry in that particular database to allow access to this database. Note that, once you have access to a database, you’re still not allowed anything by default: you need permissions to select data from tables, for example. Unless that permission has been granted to ‘public’ (or to your specific database user) by the table owner, you won’t be allowed to query the table.
For simplicity, the ‘guest’ user, and ‘user groups’ were omitted from this explanation. Also, ‘roles’ can have an impact on these access authorisations. See the ASE System Administration Guide for full details.
Transation modes:
| – chained mode – This is ANSI standard. You are always in a transaction. You never have to issue BEGIN TRANs because it is issued implicitly as soon as you connect, and then as soon as you finish previous transaction. So as soon as you issue explicit COMMIT TRAN or ROLLBACK TRAN – you will get into a new transaction. It looks strange – you only close transactions with commit or rollback – and never have to open it, because it is always already opened for you.
– unchained mode (default for Sybase) – Each individual statement is atomic. If you want to put several statements in one transactions – you have to explicitly issue BEGIN TRAN and at the end do COMMIT or ROLLBACK. Stored procedures remember in which mode they were compiled – and will not run in a different mode – unless you define them with “anymode”. |
| Read – put shared lock, update or insert – exclusive lock.
Table locks may be a result of lock promotion, but more often they happen because of your SQL / indexes. Normally table read puts a shared lock. But if you have an UPDATE or a DELETE with a WHERE clause which has a table scan – you will get an exclusive table lock without any lock promotion, regardless of locking scheme. Switching to row level locking (DATAROWS locking scheme) may not solve your problem and is very expensive: your data takes up more space, UPDATES start causing a lot more fragmentation, and you need A LOT more locks. Lock promotion works by number of locks used, and since DATAROWS uses considerably more locks, you’ll hit promotion threshold much sooner. Also if you run out of locks – you will also get a promotion to a table lock. Also, if you are committing more rows at a time than the HWM (high lock promotion threshold) – then you will still get lock promotion even with datarows locking scheme. There are 3 locking schemes: – ALLPAGES (data and indexes) – DATAPAGES (only data) – DATAROWS (only data, row-level locking) Use sp_lock and sp_objectstats to see if multiple connections/users attempt locks on rows on the same page. Use sp_sysmon to detect lock promotion. Lock promotion thresholds : LWM = 200 – never attempt a promotion below 200 locks HWM = 200 – always attempt promotion once 200 are reached (HLW – High WaterMark, LWM – Low WaterMark) Number of locks = 35,000 Note: Switching to DATAROWS might help your blocking, but maybe the problem isn’t that people are trying to read the same row, or even different rows on the same page. It might be that they’re blocking on data vs index access. DATAPAGES is a good first step to try since it eliminates index locking and usually this is all you need. It has most of the benefits of DATAROWS without the extreme overhead. It’s always a good idea to try DATAPAGES first. Also, the solution to avoid table locks may be simply to increase the lock promotion thresholds. Note: changing from ALLPAGES to DATAPAGES or DATAROWS is an expensive operation. Switching between DATAPAGES and DATAROWS is very easy and fast. Note: if a WHERE clause has local variables – optimizer will not use index, thus causing a table scan. Example:Modifying locking scheme: alter table authors lock datarows |
Adaptive Server global variables
Global variables are system-defined variables updated by Adaptive Server while the system is running. Some global variables are session-specific, while others are server instance-specific. For example, @@error contains the last error number generated by the system for a given user connection.
| @@connections | Returns the number of user logins attempted. |
| @@error | Returns the error number most recently generated by the system. |
| @@identity | Returns the most recently generated IDENTITY column value. |
| @@isolation | Returns the value of the session-specific isolation level (0, 1, or 3) of the current Transact-SQL program. |
| @@servername | Returns the name of Adaptive Server. |
| @@spid | Returns the server process ID of the current process. |
| @@sqlstatus | Returns status information (warning exceptions) resulting from the execution of a fetch statement. |
| @@trancount | Returns the nesting level of transactions in the current user session. |
| @@version | Returns the date, version string, and so on of the current release of Adaptive Server. |
Why did you decide for a career of a pilot?
Say them that you enjoy flying, and that you believe to be a responsible person. You can also say that you have all skills and strengths needed for this type of work. Alternatively you can refer to an experience that motivated you to pursue the career, such as meeting a pilot when you were young, or having passion for aircraft models since childhood.
The key is to convince them that you really want the job, and did not apply just because you’ve already spent a small fortune on your studies and training.
FAQ
How will you performance tune a query in Sybase database?
- Specify Growth Projection. If you’re using ASE 15, it’s for a reason. …
- Tuning Indexes. …
- Mind Foreign Key Constraints. …
- Monitor CPU Usage. …
- Avoid Using Co-Related Subqueries. …
- Collect Performance Data. …
- Lock, Lock, Lock. …
- Process in Several Partitions.
How can I improve my database performance interview?
- Prefer to use views and stored procedures in spite of writing long queries. …
- It’s better to introduce constraints instead of triggers. …
- Make use of table-level variables instead of temporary tables.
- The UNION ALL clause responds faster than UNION. …
- Prevent the usage of DISTINCT and HAVING clauses.
What is a Sybase database?
