Hi, i have a sql: select * into new_table from existing_table where 1 = 0
Basically, it just creates a new table (new_table) wth the same columns as the existing_table. The where 1=0 makes sure no data is selected.
Now this sql is taking more than 1 hour to execute, when it should be done in a seconds. Why is this taking such a long time? Correct me if i am wrong, but i dont think it's waiting on locks on the existing_table as 1) it's a select 2) 1=0 means no data are selected anyway
Dont understand what could possibly causing this sql to take so long to execute.
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
SELECT acquires only READ/SHARED lock but if existing table has even a single record/block/entire table locked due to already executed UPDATE/DELETE/INSERT and neither committed nor rolled back, your new table creating select has to wait till lock is released by commit/rollback or explicit killing of lock/session.
This should return in less time than you can see it happen. When you put an explicit SARG on a query that Sybase can evaluate at optimization time that will unambiguously prevent any rows from returning, it won't even try to access the data pages; just the catalog tables.
Try a test. Remove the "into newtable" and just run the "select * from oldtable where 1=2" and see what happens.
A problem like this is very rare and probably indicates you have some not-to-nice problem with your database or application. Your database might be full, the log might be full, the catalog tables might have a corruption. None of the things likely to cause this problem are good. You are just seeing a symptom of something else.
BTW, what version of Sybase are you running? Post a "SELECT @@version" if you would. THX
Regards,
Bill
"This should return in less time than you can see it happen. When you put an explicit SARG on a query that Sybase can evaluate at optimization time that will unambiguously prevent any rows from returning, it won't even try to access the data pages; just the catalog tables."
Yes that's exactly why i am confused. I think the previous posters were incorrect, in that it does NOT wait on the insert/update locks due to where 1=2. Unfortunately this sql is part of our batch process that runs in the middle of the night, which is what makes this very frustratring. If i run it manually in dbartisan during another time, there is no issue at all. And this issue doesnt happen every night, but often enough to cause a problem.
The existing_table is a normal table, not a view. I guess what i am looking for is how to go about debugging this, or some possible causes. Dont have access to db now, but version is ase 12.5
You will probably have to sit up with this thing at night to see what is really going on. Perhaps there is something else in the nightly batch stream that is hammering the database and/or locking up resources. Are there decent logs of the batch operations including the one you are running? You might want to troll through those and see if there is anything going on that nobody has discovered yet. Probably most productive to look at the nights when your job hung up.
Another approach would be to put a bunch of diagnostic queries in your job to see what is going on. Tempdb filling up, log full, CPU bound, I/O bound, huge rollback in progress, etc. You can also setup sp_sysmon to run during your nightly batch cycle which may uncover other issues.
One worst case is that someone wrote a piece of SQL that actually acquires and holds a lock on one or more system tables in tempdb. This would be very bad though it is not very likely.
You could also have a TEMPDB that is filling up. That might prevent the table creation from finishing as there is no place to write the log of the system table changes.
What is the precise version of ASE 12.5? If you are running 12.5.0, you are not on a stable release and the first thing to do is patch the installation up to something that runs reliably. Try doing a....
SELECT @@version
and posting the results. If you are running a late enough version, you can create an additional tempdb and bind your login to that tempdb and see if that helps.
Regards,
Bill
Hi bill, thanks for the info it's very helpful. I am in the process of adding the monitor to our process. I will have it to spawn a separate thread and run all the diagnostic sqls then dump the result to me in a bing.
Unfortunately our database is controlled by a separate dba team, and those guys are non responsive whatsoever. So without admin access, i am limited to execute sqls only, no logs.
I did notice in the code, there are a lot of select * into tempdb..#some_tmp_table from table. I guess that's fine since they are temp tables. Do you know what happens if there are too many temp tables created in tempdb to fill up all the space at certain time. Once tempdb is full, does it cause table creation to halt until more space are made availiale in tempdb again? If that's true, then it is most likely what happened i think.
our exact sybase version is 12.5.4
thanks again!
Yes, if tempdb fills up, anything doing a SELECT/INTO #temptable will be blocked. Anything writing to an existing tempdb table will be blocked as well. There should be Sybase log messages to indicate the database is filling up. It may be your DBA(s) are simply never bothering to check it.
It is not the number of tables in tempdb, it is the total amount of data and log space used.
You can use the sp_spaceused stored procedure or Sybase Central to monitor tempdb and see if it fills up. You can even write a script that runs say once every 5 minutes for a few hours at night and dumps the results in a file you can check in the morning.
Another approach would be to run sp_sysmon and sample during the time your job(s) run at ngiht.
One thing that usually works in your organizational situation is to make a request for whatever privs you need to troubleshoot this issue. Certainly, viewing the Sybase ASE server log (not the transaction log) is one of those things but go ahead and make your wishlist. When/if the DBAs refuse to give them to you, go to your boss and tell him that the problem is a DBA-related issue and that they won't fix it nor give you the privs to investigate it yourself. One of two things will happen. Either the DBAs will figure out what the problem is and fix it or they will give you the privs. If they do nothing, the owness is on your boss to escalate it in IT; something you cannot be expected to do. Either way, you are not caught in the middle any more.
Regards,
Bill
Thanks bill, 1 last question before i close this out. When a tempdb gets full, i know you said it prevents the creation of tables etc in tempdb, but does it prevent the creation of normal (non temp) tables (using select into for example) on OTHER databases that resides on the SAME server as tempdb.
I tried but couldnt find a good answer to this on google. Think it does, but just want to be sure.
No, not directly. The real answer is It depends on what is going on in the SELECT portion of the SELECT/INTO.
If the SELECT requires a work table (e.g. is something more complex than SELECT * FROM tblname WHERE 1 = 2) it will be blocked. Work tables are light-weight tempdb temp tables created and used within a query itself. They are "light-weight" because ASE does not bother to make entries in the system tables in an effort to speed things up.
A full tempdb is really bad because of the work table issue. For example, you cannot do a group by in a query in a database when the tempdb is full because there is no place to put the work table.
Regards,
Bill
Business Accounts
Answer for Membership
by: gagaliyaPosted on 2009-03-18 at 11:09:01ID: 23921692
want to add new_table does not exist, select into sql creates the table.