I have a report I am working on that requires data from two different places, one is a SQL Server database and the other is a DB2 database I've done this with the following code.
This is my SQL Server database:
private List<MerchantTerminal> GetListOfActiveTerminals(Int64 corebankingRecord, Int64 terminalRecord, bool dialup, bool ethernet, bool cellular, bool wifi, bool merchantAdviceBypass, bool merchantRefundBypass, bool authAdviceOnly)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select distinct ");
sql.Append(" POSH5_Prod_MerchantTerminalDetails.id ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.merchantRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.terminalID ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.TerminalRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.MCCRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.PINPadRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.terminalRentalFee ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.terminalRentalFeeCurrencyRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.DialUp ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.Ethernet ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.WiFi ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.GPRS ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.Internet ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.PrimaryCurrencyRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.SecondaryCurrencyRecord ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.TIP ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.AllowManuallyKeyedTransactions ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.MinimumTransactionVolume ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.MinimumTransactionVolumeCurrencyRecordID ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.MinimumTransactionVolumeFee ");
sql.Append(", POSH5_Prod_MerchantTerminalDetails.MinimumTransactionVolumeFeeCurrencyRecordID ");
sql.Append(", POSH5_Prod_MerchantDetails.bypassAdviceVerification ");
sql.Append(", POSH5_Prod_MerchantDetails.bypassCreditVerification ");
sql.Append(", POSH5_Prod_MerchantDetails.expireBypassCreditDate ");
sql.Append("from POSH5_Prod_MerchantTerminalDetails ");
sql.Append("left join POSH5_Prod_MerchantDetails on POSH5_Prod_MerchantTerminalDetails.merchantRecord = POSH5_Prod_MerchantDetails.id ");
sql.Append("where POSH5_Prod_MerchantTerminalDetails.active=@active ");
sql.Append("and POSH5_Prod_MerchantDetails.corebankingRecord=@corebankingRecord ");
//if (merchantAdviceBypass == true)
//{
// sql.Append("and POSH5_Prod_MerchantDetails.bypassAdviceVerification=@merchantadvicebypass ");
//}
//else
//{
// sql.Append("and POSH5_Prod_MerchantDetails.bypassAdviceVerification = 1 OR POSH5_Prod_MerchantDetails.bypassAdviceVerification = 0 ");
//}
//if (merchantRefundBypass == true)
//{
// sql.Append("and POSH5_Prod_MerchantDetails.bypassCreditVerification=@merchantrefundbypass ");
//}
//else
//{
// sql.Append("and POSH5_Prod_MerchantDetails.bypassCreditVerification = 1 OR POSH5_Prod_MerchantDetails.bypassCreditVerification = 0 ");
//}
if (terminalRecord > 0)
{
sql.Append("and terminalRecord=@terminalRecord ");
}
sql.Append("AND (");
bool addedCommMethod = false;
if (dialup)
{
sql.Append("POSH5_Prod_MerchantTerminalDetails.DialUp=1 ");
addedCommMethod = true;
}
if (ethernet)
{
if (addedCommMethod)
{
sql.Append("OR ");
}
sql.Append("POSH5_Prod_MerchantTerminalDetails.Ethernet=1 ");
addedCommMethod = true;
}
if (cellular)
{
if (addedCommMethod)
{
sql.Append("OR ");
}
sql.Append("POSH5_Prod_MerchantTerminalDetails.GPRS=1 ");
addedCommMethod = true;
}
if (wifi)
{
if (addedCommMethod)
{
sql.Append("OR ");
}
sql.Append("POSH5_Prod_MerchantTerminalDetails.WiFi=1 ");
addedCommMethod = true;
}
sql.Append(") ");
sql.Append("order by terminalID; ");
var merchantTerminalRecordList = new List<MerchantTerminal>();
var serialnumberlist = PopulateSerialNumberList();
using (SqlConnection connectionMSW = new SqlConnection(_connectionString))
{
connectionMSW.Open();
SqlCommand commandMSW = new SqlCommand();
commandMSW.CommandTimeout = 600;
commandMSW.CommandText = sql.ToString();
commandMSW.Connection = connectionMSW;
commandMSW.Parameters.AddWithValue("@terminalRecord", terminalRecord);
commandMSW.Parameters.AddWithValue("@active", true);
commandMSW.Parameters.AddWithValue("@corebankingRecord", corebankingRecord);
commandMSW.Parameters.AddWithValue("@merchantadvicebypass", merchantAdviceBypass);
commandMSW.Parameters.AddWithValue("@merchantrefundbypass", merchantRefundBypass);
using (SqlDataReader reader = commandMSW.ExecuteReader())
{
while (reader.Read())
{
merchantTerminalRecordList.Add(PopulateMerchantTerminalRecord(reader, serialnumberlist));
}
}
}
return merchantTerminalRecordList;
}
And this is my DB2 database:
private POSHData PopulateSerialNumberList()
{
POSHData result = new POSHData();
List<POSHSerialNumber> serialResults = new List<POSHSerialNumber>();
StringBuilder sql = new StringBuilder();
sql.Append("select distinct termcert.termid, termcert.termserno, termcert.PPADSERNO, termcert.appname, termcert.appversion, terminal.TERMTYPE, terminal.PINPADID, terminal.LASTTRAN, terminal.EDCCRSRC, termcfg.tranauthonly, termcfg.tranadjust from posh.termcert as termcert LEFT JOIN posh.terminal as terminal ON termcert.termid = terminal.termid LEFT JOIN posh.termcfg as termcfg ON termcert.termid = termcfg.termid");
DataSet dsResult = new DataSet();
using (DB2Connection connectionPOSH = new DB2Connection(_connectionStringPOSH))
{
connectionPOSH.Open();
DB2Command commandMSW = new DB2Command();
commandMSW.CommandTimeout = 600;
commandMSW.CommandText = sql.ToString();
commandMSW.Connection = connectionPOSH;
DB2DataAdapter adapter = new DB2DataAdapter();
adapter.SelectCommand = commandMSW;
adapter.Fill(dsResult);
}
foreach (DataRow item in dsResult.Tables[0].Rows)
{
POSHSerialNumber found = new POSHSerialNumber();
found.termid = item["termid"].ToString().Trim();
found.terminalSerialNumber = item["termserno"].ToString().Trim();
found.pinPadSerialNumber = item["PPADSERNO"].ToString().Trim();
found.appName = item["appname"].ToString().Trim();
found.appVersion = item["appversion"].ToString().Trim();
found.terminalRecord = dataTypeConversion.ToInt64(item["TERMTYPE"].ToString().Trim());
var pinPadRecord = dataTypeConversion.ToInt64(item["PINPADID"].ToString().Trim());
if (pinPadRecord > 0)
{
found.pinPadRecord = pinPadRecord;
found.pinPadSerialNumber = item["PPADSERNO"].ToString().Trim();
}
var LastTransaction = dataTypeConversion.ToDouble(item["LASTTRAN"].ToString());
found.LastTransaction = commonController.UnixTimeStampToDateTime(LastTransaction);
found.SettlementType = item["EDCCRSRC"].ToString().Trim();
found.tranAuthOnly = item["tranauthonly"].ToString().Trim();
found.tranAdjust = item["tranadjust"].ToString().Trim();
serialResults.Add(found);
}
result.poshSerialNumbers = serialResults;
return result;
}
I then compare my code and pull results to the front using this code with a LINQ statement
private void GetSerialNumber(POSHData poshData, MerchantTerminal foundTerminal)
{
var result = poshData.poshSerialNumbers.Find(x => x.termid.Trim() == foundTerminal.terminalID.Trim());
if (result != null)
{
foundTerminal.terminalSerialNumber = result.terminalSerialNumber;
foundTerminal.pinPadSerialNumber = result.pinPadSerialNumber;
foundTerminal.appName = result.appName;
foundTerminal.appVersion = result.appVersion;
foundTerminal.pINPadRecord = result.pinPadRecord;
foundTerminal.terminalRecord = result.terminalRecord;
foundTerminal.LastTransactionDateTime = result.LastTransaction;
if (foundTerminal.pinPadSerialNumber.Length == 0)
{
foundTerminal.pinPadSerialNumber = result.pinPadSerialNumber;
}
switch (result.SettlementType)
{
case "2":
foundTerminal.PABX = "Journal";
break;
case "3":
foundTerminal.PABX = "Batch Close Required";
break;
default:
foundTerminal.PABX = "Unknown";
break;
}
foundTerminal.AdviceText = result.tranAdjust;
foundTerminal.AuthOnly = result.tranAuthOnly;
}
This seems fine in a DEV and QA environment that have only 500-1000 records. In my PROD environment its significantly slower as we are dealing with up to 20k + records. What would be the quickest way to pull this data as of right now in prod it can take up to 5-8 minutes.
Thanks for all the help!
EDIT: After running my queries in their respective Databases, it looks like the actual slow down is within my LINQ query I use, I guess the better question is for mass amounts of data how can I optimize my linq?
I don't think there is anything fundametally wrong with how you read data, in the MS SQL part at least, in the part you showed. These days using an ORM is common (Dapper, Entity Framework) but they are not necessary.
The quickest way to pull this data to find what is slow.
Run the query in a db client (e.g. SQL Management Studio)
If slow, fix SQL.
If fast, examine how your code is called.
using) so that you know exactly which part is slow. It may not be the database call.