|
Like I said, the aliasing is working, but it takes a very long time for the query to return. In one test case, a query with aliased columns took almost 6 minutes to return; the identical query with unaliased columns took less than a second. This kind of time difference is pretty consistent, regardless of how many columns are requested, and scales closely to the number of rows returned (the more rows, the longer the aliased query takes.)
It is very frustrating.
|
|
|
|
|
I traced the problem to a piece of code that uses SqlDataAdapter.Fill to populate a DataSet, then took to the Internet.
It turns out that there is some optimization that SQL does with queries, where it tries to import indexes from underlying tables. This works fine as long as the columns in the view match up with the columns from the tables. When you alias the query's columns, however, things can degenerate into a quagmire of competing indexes; the delay is caused by SQL trying to wait out a resolution. You can get rid of the delay by instructing SQL to abandon arithmetic operations that result in an overflow or underflow. Huh? 
The solution was to prefix the query with SET ARITHABORT ON; which tells SQL to just fly over the swamp rather than try to navigate through it. After the query, tack on ; SET ARITHABORT OFF; to reset the server variable, mainly because there is probably a reason why it set to OFF in the first place.
|
|
|
|
|
Good bit of research, thanks for posting the solution, that with parameter sniffing goes into my odd box of delays to hunt for!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everybody!
Please can you tell me what is the best tool for creating a database from scratch?
|
|
|
|
|
Member 9611795 wrote: Please can you tell me what is the best tool for creating a database from scratch?
A computer?
If that wasn't the answer you thought you were looking for then you need to understand that you don't choose a database based on tool but rather on the functionality of the database vendor. AFTER you have chosen a database then you look for applications that allow you to manage the database application.
|
|
|
|
|
Pen and paper: figure out what you want the database to do for you and then create a structure to support that. Sketching it out with a pen and paper is the first thing I do (or whiteboard).
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
It's all about learning not working! so I want to know the tool which can help me to insert data and retrieve it and more.
is SQL server the tool that can help me? or ADO.NET?? and what is the difference between them??
|
|
|
|
|
Member 9611795 wrote: I want to know the tool which can help me to insert data and retrieve it and more.
SQL Server is where data is stored, ADO.NET is a set of objects for Creating, Reading, Updating and Deleting data.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Just Like Mark wrote, I start with the whiteboard.
When that gets to small I make the model in Oracle SQL Developer Data Modeler [^], which supports Oracle, SQL Server and DB2 out of the box, and you can make your own design rules as well.
And as an extra plus, it supports source control via Subversion.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
|
Hi Friends,
I have windows service which will execute a STORED PROCEDURE during a time period, fetches email IDs from DB and will send emails to them.
I am passing only one argument @ValidTillDays INT.
I made this service run on each day.
If its running for 3 days, then for the first day it executes perfectly.
For the second day and remaining all days it gives following error.
Procedure or function "BlaBlaBla" has too many arguments specified.
I have only one input parameter and I am passing only one too.
And if this was the case it could have given the error for the first time also.
Please help me if you find any clue .
Thanks 
By:
Hemant Thaker
|
|
|
|
|
Hemant Thaker wrote: If its running for 3 days, then for the first day it executes perfectly.
For the second day and remaining all days it gives following error.
Strange. Based on what you share, sounds like some logical error where you are considering the DayNo. Are you? Somehow, somewhere the current day is getting into picture while running the procedure and the error. Did you DEBUG and see? Check if so.
|
|
|
|
|
I suspect you are concatenating the parameter value and passing in a string! You might want to post the code used to build the procedure call.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your responses.
Error is resolved, that was more logical.
I was creating Command object at class level, and adding parameters to it in remainderMailtimer_Elapsed().
This event will execute on timer interval.
So when it executes first time it find one parameter and runs successfully.
after 3 minutes interval it executes event for the second time and adds same parameter to it.
so now command object will have two parameters and corresponding SP has one. So it was throwing error.
I changed the code and created command object at event level.
Thanks.
By:
Hemant Thaker
|
|
|
|
|
Hi
Our ERP system has a stock master table. It consist of the fields 'warehouse', 'product' and a bunch of other fields relating to the stock item.
A product can exist in various warehouses, so it is the warehouse +product combination that is unique.
How would the primary key (if any) be set on this table?
|
|
|
|
|
A primary key (often) consists out of multiple columns. I'd suggest putting the primary key on BOTH, and to add an autoincrement-field and make that unique. Use the autoincrement-column to make relations to other tables.
ALTER TABLE dbo.MasterTable ADD CONSTRAINT PK_MasterTable
PRIMARY KEY CLUSTERED (WareHouseId, ProductId); (And call the autoincrement "MasterTableId")
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddie, but you have totally lost me?
If I lok at the two lines:
ADD CONSTRAINT PK_MasterTable
Prevents any duplicates in MasterTable column
and
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID?
Or are there three Primary keys now?
If you look at the table in MSQuery, the Product is bold, so is this the primary key?
|
|
|
|
|
Richard.Berry100 wrote: Prevents any duplicates in MasterTable column
No, it doesn't; it's the part behind that which says "which type" of constraint. It's a single statement, not two separate statements.
Richard.Berry100 wrote:
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID?
Yup.
Richard.Berry100 wrote: Or are there three Primary keys now?
One (compound) primary key, consisting of two columns, and the name of that constraint is "PK_MasterTable".
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: WharehouseID
I see what you did there. 
|
|
|
|
|
Mycroft Holmes wrote:
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key. |
That's a possibility; then again, it introduces a concatenation-action, and we'd be storing redundant information. It'd also affect performance; having a large varchar-based key (as two bigints as Id's or Guids would be concatenated to a varchar) would be not-nice for your indexes.
Or, in the words of my teacher; it would no longer be an atomic value.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Friends i am going to develop a website which is like you tube.so i want to play an add first then i want to play the video on clicking the video file.so help me in writing the code. i wish to use two databases one for adds and other for videos.so please help me writing code
Sundeep
|
|
|
|
|
Please do not post questions asking people to write your code for you. Members of this site come here to help people who make an effort to learn and do their own work. If you do not know how to begin to create your project then find some online study guides, or buy some books.
|
|
|
|
|
If you are looking to get the code written then try Elance or one of the other code writing sites. CodeProject is for people who want to write their own code and need a little help learning.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi - I have having a join issue.
we have report which was set up a while ago, needs to be modified.
I have to add one more table, using a LEFT OUTER JOIN but using (+)!
I need to left outer join table B to table A, but it is a substring.
I tried which made sense syntax wise -
substr(A.source,1,4)=substr(B.OFFERNO,1,4)(+)
which did not work.
Below syntax did not error out - does this look right?
substr(A.source,1,4)=substr(B.OFFERNO(+),1,4)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
