|
You're welcome. But, I needed the practice anyway, and besides, once I got thinking about the problem, I couldn't get it off my mind. I'll probably used what I learned in some other project.
True, my Tests table only had 48 rows, but I don't believe that is the issue. If you carefully consider my query, you'll see that the DB engine only has to scan the Cars table once, and (assuming both Tests.CarID and Tests.TestTypeID are indexed) the Tests table ONCE! That is the key. I believe the reason yours is running slowly is due to the complexity of the table structure, not because you're attempting a crosstab.
If it's utterly impossible to simplify the table structure, or to dump just enough of the tables into a simplifed structure so this query will run quickly, then you may be right about not doing it this way.
Good Luck.
|
|
|
|
|
Yeah, I can see your query would run faster but my database is far more complicated than this example. But you're right! I have to look at the structure, indexes, etc, of my database and see if I can make this query look like yours. However, I don't think it can look like yours because it's very dynamic query and I don't want to create different queries for each cases - I'm just modifing parts of the query. Maybe I shouldn't be so lazy and write a dedicated queries for each of the cases (even though it's against the best programming practices).
... No wonder why there's so many database jobs out there =)
Thank you again!
-- modified at 16:47 Friday 13th April, 2007
|
|
|
|
|
I wanted to know if it is possible to get the data in parts.
I mean my application has many records in the table, so for a sql query like select * from table, there is lots of records to be read from the ResultSet.
Is there any query like
select (first 100) * from table
then
select (next 100) * from table
.
.
.
something like this ?
please help
|
|
|
|
|
select top 100 records from table where id > @id The first time in, pass in an @id of 0. Next time, pass in the maximum id of the previous results.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
You are right that it is a simplistic solution. I find that it fits a lot of cases though.
A lot of time when you sort/shuffle the id's around, this is done by retrieving the data in one go and performing the sorting/shuffling on the server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
SELECT *<br />
FROM Table <br />
WHERE RowNumber Between @FirstRow AND @EndRow
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
I still didnt get what to do
How should the above command be written as ? my table name is 'orders' and there are only 2 fields in that 'ORDERID' and 'ITEMDESIRED'
There is no primary key as such. Yes I can make 'ORDERID' the Primary key though.
What should be the '@FirstRow' and '@EndRow' values be?? or should I write them just like that. Please help, its urgent.
I am using the ojdbc14.jar to connect to the Oracle 10g database.
I read somewhere about the LIMIT and OFFSET command, but tried it in Oracle SQL* Plus and saw that it does not work. Also it gave the same exception when tried to run the command through my Java program.
The command I ran is
<br />
select * from orders limit 10;<br />
and the Exception I got is
<br />
ORA-00933: SQL command not properly ended<br />
|
|
|
|
|
The example query I gave works fine for SQL Server 2005
@FirstRow = row, from which you want to start getting the results
@EndRow = last row of your results
In your case, for exapmple, it can be
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID BETWEEN 5 AND 25
OR, if this does not work, in your db, try:
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID > 5 AND ORDERID < 25
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
i problem
in a database i do i find how many feilds name id in database
|
|
|
|
|
You have another problem. The inability to read posting guidelines.
You've already posted about this, so don't start a new thread. It's not polite.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O'Hanlon wrote: another problem. The inability to read posting guidelines
|
|
|
|
|
I am using MS Access 2003 with VB.Net 2003. In one of my Table A field(ResignDate) is of type Text
I am writing following query to retrieve the name and other information of employee who resigned before a specific date which is to be given in the condition.....
my query is..
Dim sql16 As String = "SELECT Employee_AutoID,NameInitial,NameInitial+' '+Name " & _<br />
"AS Emp_Name,Designation FROM tblEmployeeMaster " & _<br />
"WHERE Resign_Flag='No' OR CDate(ResignDate)< #" & endDayOFFinancialYear & "# "
what is wrong in the query....
Tirtha
Miles to go before I sleep
|
|
|
|
|
remove Cdate function
resigndate='"+enddayofffinancialyear+"'
|
|
|
|
|
how do we count stored proc in database
how do we count system difined stored proc and userdifined stored proc
|
|
|
|
|
The stored procedures in sql are objects so whenever a stored procedure is created its information is stored in the system table sysobjects, and as it is a sp the type would be 'p'
Thus to know the number of stored procedures for a database the query would be
select count(*) from sysobjects where type='p'
Gautham
|
|
|
|
|
thanks but now i have another problem
in a database i do i find how many feilds name=id in database
|
|
|
|
|
I didnt get that???
Gautham
|
|
|
|
|
i mean that in a database there are three table
emp1
emp2
emp3
all table have a column name id
how to count number of table with column id
|
|
|
|
|
select * from sysobjects t1 join syscolumns t2 on t1.id=t2.id where t2.name="columnname"
i guess, not sure though!
Gautham
|
|
|
|
|
This will show all tables where there is a column named id
SELECT TABLE_NAME
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id';
Here you are counting how many such cases are in your db.
SELECT COUNT(COLUMN_NAME)
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id' ;
Note: This is correct for SQL 2005
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
Hi all,
I'm using interopt object to handle data in an excel file, like that:
Excel.Application ExcelObj = new Excel.Application();<br />
<br />
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(FilePath, 0, true, 5,<br />
"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);<br />
<br />
Excel.Sheets sheets = theWorkbook.Worksheets;<br />
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(SheetIndex);<br />
<br />
Excel.Range range = worksheet.get_Range(BeginCol + BeginRow.ToString(), EndCol + BeginRow.ToString());<br />
System.Array myvalues = (System.Array)range.Cells.Value2;<br />
With cells formated as DateTime datatype, this method didn't get the correct format...Ex: 04/11/2007, it'll get as 39183.00...
How can I get the correct value? Please help me.
Thank you.
|
|
|
|
|
Hi i have doubt in roles using in vb.net...,
in my sqlserver for a paricular database,i created two application roles..and i given permission for those roles also..,
Now how to tell the roles in vb.net..,
(i.e) how to interact with those roles from vb.net codes...,
I dont know how to interact with those roles...,
so plz give me some idea..,if any sample codes will be there...,
it will be more usefull for me...,
Magi
|
|
|
|
|
i have one column datatype as datetime
i want to reaplce year of all date to 2030
can i use repalce and left functionbut its showing error plz help
|
|
|
|
|
REPLACE and LEFT functions take character expressions.
hope you might have used datetime data directly in the functions.
convert datatime to character and then use it in replace & left
Regards
KP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
