I have a view that pulls about 200 columns from a table, no joins. The procs that use the view only use about 10 columns from it. Does having the extra 190 columns have a significant impact on performance using the view? EDIT: Just to clarify based on original questioner's comment, the query in his proc only uses 10 columns out of 200. ...
I have the following Select statement, but want to change it to use iner joins as I believe they are more efficient, but not too sure where to start. DECLARE @myNameID int DECLARE @myAddressID int DECLARE @myFirstName nvarchar(256) SET @myNameID = 1 SET @myAddressID =1 SET @myFirstName='Nathan' SELECT @myNameID = myNameID FROM ...
Hi all, I am storing pages for websites in a 'pages' database table, they are referenced by their path (i.e. 'articles/my-first-blog-post'), I need to select all the children of a particular page, but without selecting grandchildren. So if I run: SELECT * FROM pages WHERE path LIKE 'articles%' I'll get pages with the following paths:...
Our team has been asked to write a Web interface to an existing SQL Server backend that has its roots in Access. One of the requirements/constraints is that we must limit changes to the SQL backend. We can create views and stored procedures but we have been asked to leave the tables/columns as-is. The SQL backend is less than ideal....
I am working with a Java prepared statement that gets data from an Oracle database. Due to some performance problems, the query uses a "virtual column" as an index. The query looks like this: String status = "processed"; String customerId = 123; String query = "SELECT DISTINCT trans_id FROM trans WHERE status = " + status + " AND FN_GE...
Sometimes we load data from a lot of sources, like files, streams, etc. Well ... to do it with low headaches, we usually disable foreign keys checking, the problem is that when we do get some foreign key constraint violations that we are not warned of that. How can we check this after restoring foreign key constraints? ...
I would like to know if there is anyway I can set one of my stored procedure parameter as optional. IF @thing_id <> '' BEGIN SET @sFiltre = @sFiltre + ' AND OPERES.OPE_THING = ' + CONVERT(VARCHAR,@thing_id) END ...
Given the following: if object_id('MyTable') is null create table MyTable( myColumn int ) Is it not possible that two separate callers could both evaluate object_id('MyTable') as null and so both attempt to create the table. Obviously one of the two callers in that scenario would fail, but ideally no caller should fail, rather one sh...
We've got a web-based application. There is a time-bound database operation (INSERTs and UPDATEs) in the application which takes more time to complete, hence this particular flow has been changed into Java Thread so it will not wait (block) for the complete database operation to be completed. My problem is, if more than 1 user come a...
Hi is it possible for me to do this somehow? When i run the statement i get an exception, @Price_Plan is not delared, so obviously the adhoc query does not have scope to access @Price_Plan. Is there a workaround, or a better way to query a table whose name changes per execution of this query. DECLARE @Price_Plan varchar(3), @MNP_Network...
I have a simple table where one of the field is a date column. I can select the most recent X records by doing Select * from MyTable order by last_update desc limit X But how do I efficiently delete those column? Is subselect the quickest? ...
Hey all, I have a question regarding multi-table update(MySQL). Consider table t1 and t2. The PKEY for t1 is 'tid' which is a foreign Key in t2. There is a field "qtyt2" in t2 which depends on a field called "qtyt1" in table t1. Consider the foll SQL statement: UPDATE t2,t1 SET t2.qtyt2=IF(( t2.qtyt2- t1.qtyt1 )<0,0,( t2.qtyt2- ...
Say for some reason I have employees in two separate tables, employee1 and employee2 I just want to add them together, as if they are stacked on top of each other. something like: select all from employee1 and employee2 where name = bubba i know im generalizing, this will be in postgres eventually so if there are any specifics there...
PL/SQL is not my friend recently. I am getting the above error with the following query: SELECT cr.review_sk, cr.cs_sk, cr.full_name, tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt", cs.cs_id, cr.tracking_number from review cr, cs, fact cf where cr.cs_sk = cs.cs_sk and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%' and row_delete...
I use java.util.StringTokenizer for simple parsing of delimited strings in java. I have a need for the same type of mechanism in pl/sql. I could write it, but if it already exists, I would prefer to use that. Anyone know of a pl/sql implementation? Some useful alternative? ...
Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range. Looking for a single sql query which has a between start and end date in the where clause which can handle both cases where the dates are either both null or both have values. I don't want to use an IF statement. ...
I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code : select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME) but this is not very elegant, and probably not very fast either. Is there a 'better way t...
I'm performing some MySQL table maintenance that will mean removing some redundant columns and adding some new ones. Some of the columns to drop are of the same type as ones to add. Would the procedure be faster if I took advantage of this and reused some of the existing columns? My rationale is that changing column names should be a s...
This is beset illustrate by an example that I think should work but doesn't: select * from TABLE_A where KEY in ( select substr(DYNAMIC_KEY,3) from TABLE_B where DYNAMIC_KEY like '$$%' and RECORD_ID='104251893783388824'); Basically, the inner select statement return a set of result which had a '$$' prefix. This is use as a lookup ke...
I am trying to execute a query against a MySQL database. The query is fairly complex it has 5 inner joins, including 1 join to itself and it returns 3 pieces of information from 2 different tables. We are using hibernate and till now I have used it for simple queries only. I have written the sql query and tested it too. I am wondering ...