|
- I saw Dynamic Sql is taking lot of time for execution, even if I use it with most possible ways when Data becomes little bit, the difference in execution time is too huge.
- No I saw now that I have use Columns as well randomly - is there any way like compare with sysColumns and then get its type then join with it etc? Anything works for me without using dynamic sql.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi,
I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below
TableName, ChangedColumnName, OldValue, NewValue
Table123 , ColA, 123 Greetings 123 New Greetings
Table123 , ColC, 123 World 123 Hello World
Table123 , ColE, 123 Test World 123 New World
etc.
Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You lost me with the "three columns that are different in these two rows" - how can you have different columns in specific rows of a single table?
You've also mentioned that your dynamic SQL is taking a long time to run - I don't believe that is because it is dynamic SQL. It's more likely to be the way you have constructed the actual query.
If you share the code you use to build the dynamic query it might help me to understand what you are trying to achieve (and we might also be able to solve your performance problem)
|
|
|
|
|
Chilli is right, dynamic sql is unlikely to be your problem, query design is almost certainly the cause of slow processing.
You need to create a temp table that hold all the field from the various sources and feed the records into that table to make the structure the same.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah Could be but most of the times I used Dynamic Sql if the rows are in like 10000s or 100000s then performance of the Stored Procedure decreased even after enabling and disabling the Query plans in both situations it decreased heavily, but I was able to bring that up 10 or 20 minutes, which is not bad for ETLs. But then the Politics involve and make us inferior just for that reason. That's what happens most of the times. But yes normal queries I ran so far run within couple of minutes but the dynamic sql took 10s or 20s of minutes and I could not able to minimize it more than that.
Any help would be greatly helpful - thanks for all help my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Have you taken the script of the dynamic query and run a query profile on it to see if there are indexes that can be tuned.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Quote: Any help would be greatly helpful - thanks for all help my friends.
As I said last week, if you share the code that you are using to generate the dynamic SQL we may be able to help you improve it
|
|
|
|
|
Hi,
I am trying to run my application in debugging mode. Logon screen appears and when I try to login "context::Context(void) - cs_ctx_alloc() failed" error is encountered.
On debugging using break point and selecting continue when the code crashes, following Database Access Error Message is displayed
"<ctsybasedaport::ctsybasedaportdanger>:ct_con_props(CS_USERDATA)failure".
Following are the environment setup in which I am finding error.
OS: Windows 10
IDE: Visual Studio 2013, VC++ Code
Access: Elevated
Database: Sybase OCS 15_0
The application is working well on following environment:-
OS: Windows 7
IDE: Visual Studio 2013, VC++ Code
Access: Admin
Database: Sybase OCS 12_5
|
|
|
|
|
My task is: Our users get from their chemical suppliers some tables do determine the required quantity for chemical Y based on the given quantity of chemical X.
BTW: The task is_not_ to find a linear regression formula.
Easy example- 2 Dim: Depending on X the component Y has to be used with different quantities:
X Y= f(X)
1 0.1
2 0.2
3 0.5
In Praxis this will be not only y= f(x) it is more r=f(x,y,z, ...) and much more it is (r, s, t, ...)= f(x, y, z, ....) but the later is not the discussion here.
Now I'm asking me how one can design a General database layout with a constant number of tables/fields to save an N dimensional function.
With constant number of tables/fields I mean I don't like to solve this task by creating "dynamicly" columns according to the bigest dimension in use... or so 
Note: I have also to say most probably I’m not going to save this kind of data in a set of related tables, but anywhere I’m interesting how one would do it theoretically, maybe this gives me some more ideas and last but not least to learn.
The dimensions are usually around 3 to 5, but I like to solve: How can one design a General Database Table Layout -for N dimensions - for this task?
Is started like this
TBLS
ID (P)
NAME
TBLS_BASES
ID
TBLS_ID (P) FK: TBLS.ID
BASE_ID (P)
BASE_NAME
TBLS_BASES_VALUES
ID
TBLS_ID (P) FK: TBLS_BASES.TBLS_ID
TBLS_BASES_ID (P) FK: TBLS_BASES.BASE_ID
POS (P)
VALUE
TBL_VALUES
TBLS_ID FK: TBLS_BASES_VALUES.TBLS_ID
TBLS_BASES_ID FK: TBLS_BASES_VALUES.TBLS_BASES_ID
POS FK: TBLS_BASES_VALUES.POS
VALUE
Now an example for three dimension z= f(x, y)
First the three "base" tables for the function
TBLS TBLS_BASES TBLS_BASES_VALUES
====== ========================== ==========================================
ID BASE_ID TBLS_ID BASE_NAME TBLS_ID TBLS_BASES_ID POS VALUE
(P) (P) (P) (P) (P) (P)
---- ---- ------- --------- ------- ------------- --- -----
T1 T1B1 T1 e1 T1 T1B1 1 0.1
T1 T1B1 2 0.2
T1 T1B1 3 0.3
T1 T1B2 T1 e2 T1 T1B2 1 0.7
T1 T1B2 2 1.2
T1 T1B2 3 3.4
And here my Problem comes
In case of 3Dim always for 2 bases are needed to save one function value, in case if 4Dim it Needs a Group of 3 bases to save a function value, and so on...
Does any body has a sugestion for a better layout? At the moment I'm blocked and don't see a way how I can link a "Group" of items to one specific "Point".
TBL_VALUES
==========================================
TBLS_ID TBLS_BASES_ID POS VALUE
------- ------------- --- -----
T1 T1B1 1 1
T1 T1B2 1 1
T1 T1B1 1 2
T1 T1B2 2 2
T1 T1B1 1 3
T1 T1B2 3 3
T1 T1B1 2 4
T1 T1B2 1 4
T1 T1B1 2 5
T1 T1B2 2 5
T1 T1B1 2 6
T1 T1B2 3 6
T1 T1B1 3 7
T1 T1B2 1 7
T1 T1B1 3 8
T1 T1B2 2 8
T1 T1B1 3 9
T1 T1B2 3 9
Sorry for my english and thank you in advance for some tips.
Bruno
[Edit]
Sorry for that, after about 2h edit I hope it is finally done, but preview does not really works nice 
Ok, it was not only preview
modified 19-Jan-21 21:04pm.
|
|
|
|
|
In your TBLS have a parent ID which is self referencing. I would also store a text structure of your formula in that table.
This is a straight forward bill of materials structural requirement.
Have you considered a mathematical formula parser it may allow you to store the text and decompose it to elements on the fly (caveat I have no idea of their capabilities or accuracy).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for your Response. Yes finally I will save it as text and parse it. It is only curiosity how a structure should look for a relatinal DB.
After reading agein
Quote: In your TBLS have a parent ID which is self referencing
Great idea, thank you so much!
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Hi,
I am getting the following error when running the sql script
Cannot initialize the data source object of OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null)
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=\\xxxx\xxx\xxx\DataExchange\xcxcxcx\xxxx\;HDR=Yes;Format=TabDelimited',
'SELECT * FROM TIN_Solicitation_Response_Error_01032017.txt')
I tried online reading different articles and answers, I could not get answer for it. Any help like why does it happen, when it happens and any fixes etc can be greatly helpful.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 5-Apr-17 17:03pm.
|
|
|
|
|
Have you confirmed that the file exists in that path FROM THE SERVER. Not from your local machine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
what is merge and hash join ?
if anyone know and use it then please share the knowledge.
when one should go for merge and hash join.
if possible discuss it with a good example the significance of merge and hash join.
also tell me how merge and hash join is different from normal join
thanks
tbhattacharjee
|
|
|
|
|
|
CHill60 wrote: Google is a wonderful resource
OP is pathologically averse to using Google. Maybe it's a rude word in his language.
|
|
|
|
|
I see what you mean 
|
|
|
|
|
searching google for merge join in sql server brings lots of result but i like to know when it is good or should i do it when joining one key is pk or other key is fk or regular key.
in this kind of scenario merge join in sql server will be good.
share your idea if you know. thanks
tbhattacharjee
|
|
|
|
|
This article (from the first page of the Google search I posted) sums it up pretty well
Understanding SQL Server Physical Joins[^]
I draw your attention to the statement at the end of the article
Quote: SQL Server does a pretty good job in deciding which join operator to use in each condition. Understanding these condition helps you to understand what can be done in performance tuning. It's not recommended to use join hints (using OPTION clause) to force SQL Server to use a specific join operator (unless you have no other way out), but rather you can use other means like updating statistics, creating indexes or re-writing your query.
Given the way you have worded your question I would just stick to using normal join syntax and allow SQL Server to do the decisions on how they are physically implemented.
These resources may be helpful:
SQL Server Join Example[^]
Visual Representation of SQL Joins[^]
|
|
|
|
|
Tridip Bhattacharjee wrote: searching google for merge join in sql server brings lots of result but i like to know when it is good or should i do it when joining one key is pk or other key is fk or regular key.
Try reading the first pages that come back as a result 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All,
I have an application which reads all the Jobs and its related packages which failed, but its reading only those Packages which are executed as part of the Jobs. Is there any way that I can check all the Packages that are failed like the ones that are executed externally like from an Application or Manually etc.
The code to get the failed packages that are part of the SQL Agent Jobs:
DECLARE @PreviousDate datetime<br />
DECLARE <a href="https://www.codeproject.com/Members/Year">@Year</a> VARCHAR(4)<br />
DECLARE <a href="https://www.codeproject.com/Members/month">@Month</a> VARCHAR(2)<br />
DECLARE @MonthPre VARCHAR(2)<br />
DECLARE <a href="https://www.codeproject.com/Members/Day">@Day</a> VARCHAR(2)<br />
DECLARE @DayPre VARCHAR(2)<br />
DECLARE @FinalDate INT
-- Initialize Variables<br />
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day<br />
SET <a href="https://www.codeproject.com/Members/Year">@Year</a> = DATEPART(yyyy, @PreviousDate)<br />
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))<br />
SELECT <a href="https://www.codeproject.com/Members/month">@Month</a> = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)<br />
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))<br />
SELECT <a href="https://www.codeproject.com/Members/Day">@Day</a> = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)<br />
SET @FinalDate = CAST(@Year + <a href="https://www.codeproject.com/Members/month">@Month</a> + <a href="https://www.codeproject.com/Members/Day">@Day</a> AS INT)
-- Final Logic
SELECT h.instance_id, j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
-----------------------------------DATABASE-------------------------------------------------------------
CREATE TABLE ACCOUNTS(ACCNO INT PRIMARY KEY,BAL PRIMARY KEY);
insert into accounts values(77,10000);
insert into accounts values(88,10000);
---------------------------------HERE IS JDBC CODE----------------------------------------------------
import A.JDBCUtil; // ****JDBCUtil is the class having connection code
import java.sql.*;
public class Test12 {
public static void main(String[] args) {
Account acc=new Account();
acc.transfer(77,88,5000); //******LINE 8******
}
}
class InvalidAccountNumberException extends Exception{
int accno;
public InvalidAccountNumberException() {
}
public InvalidAccountNumberException(int accno){
this.accno=accno;
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "Accno"+accno+"not found";
}
}
class InsufficientFundsException extends Exception{
public InsufficientFundsException() {
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "sufficent funds are not available";
}
}
class Account{
int bal;
int sab,dab,danb,sanb;
public void transfer(int sa,int da,int amt){
Connection con=null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;
try{
con=JDBCUtil.getOracleConnection();
con.setAutoCommit(false);
ps1=con.prepareStatement("select bal from accounts where accno='?'");
ps1.setInt(1,da); ******//LINE 12******
ResultSet rs1=ps1.executeQuery();
if(rs1.next())
dab=rs1.getInt(1);
else
throw new InvalidAccountNumberException(da);
danb=dab+amt;
//updating destination account balance
ps2=con.prepareStatement("update accounts set bal='?' where accno='?' ");
ps2.setInt(1,danb);
ps2.setInt(2,da);
ps2.executeUpdate();
System.out.println("**"+da+"*updated*");
//checking source account
ps1.setInt(1,sa);
rs1=ps1.executeQuery();
if(rs1.next()){
sanb=rs1.getInt(1);
}else{
throw new InvalidAccountNumberException();
}
if(sab>=amt){
sanb=sab-amt;
}else{
throw new InsufficientFundsException();
}
//updating Source account
ps2.setInt(1,sanb);
ps2.setInt(2,sa);
ps2.executeUpdate();
con.commit();
System.out.println("**"+sa+"updated");
System.out.println("funds transferred");
}catch(Exception e){
e.printStackTrace();
try{
con.rollback();
}catch(Exception e1){
}
}finally{
JDBCUtil.cleanup(ps1, con);
JDBCUtil.cleanup(ps2, con);
}
}//transfer
}//account
|
|
|
|
|
Where is line 8, where is line 12?
|
|
|
|
|
CHECK THE CODE I HAVE WRITTEN THE LINE NO ON THE CODE
|
|
|
|
|
-----------------------------------DATABASE-------------------------------------------------------------
CREATE TABLE ACCOUNTS(ACCNO INT PRIMARY KEY,BAL PRIMARY KEY);
insert into accounts values(77,10000);
insert into accounts values(88,10000);
---------------------------------HERE IS JDBC CODE----------------------------------------------------
import A.JDBCUtil; // ****JDBCUtil is the class having connection code
import java.sql.*;
public class Test12 {
public static void main(String[] args) {
Account acc=new Account();
acc.transfer(77,88,5000); //******LINE 8******
}
}
class InvalidAccountNumberException extends Exception{
int accno;
public InvalidAccountNumberException() {
}
public InvalidAccountNumberException(int accno){
this.accno=accno;
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "Accno"+accno+"not found";
}
}
class InsufficientFundsException extends Exception{
public InsufficientFundsException() {
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "sufficent funds are not available";
}
}
class Account{
int bal;
int sab,dab,danb,sanb;
public void transfer(int sa,int da,int amt){
Connection con=null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;
try{
con=JDBCUtil.getOracleConnection();
con.setAutoCommit(false);
ps1=con.prepareStatement("select bal from accounts where accno='?'");
ps1.setInt(1,da); //**************//LINE 12******************//
ResultSet rs1=ps1.executeQuery();
if(rs1.next())
dab=rs1.getInt(1);
else
throw new InvalidAccountNumberException(da);
danb=dab+amt;
//updating destination account balance
ps2=con.prepareStatement("update accounts set bal='?' where accno='?' ");
ps2.setInt(1,danb);
ps2.setInt(2,da);
ps2.executeUpdate();
System.out.println("**"+da+"*updated*");
//checking source account
ps1.setInt(1,sa);
rs1=ps1.executeQuery();
if(rs1.next()){
sanb=rs1.getInt(1);
}else{
throw new InvalidAccountNumberException();
}
if(sab>=amt){
sanb=sab-amt;
}else{
throw new InsufficientFundsException();
}
//updating Source account
ps2.setInt(1,sanb);
ps2.setInt(2,sa);
ps2.executeUpdate();
con.commit();
System.out.println("**"+sa+"updated");
System.out.println("funds transferred");
}catch(Exception e){
e.printStackTrace();
try{
con.rollback();
}catch(Exception e1){
}
}finally{
JDBCUtil.cleanup(ps1, con);
JDBCUtil.cleanup(ps2, con);
}
}//transfer
}//account
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
