public int Compare(CorrelationModel x, CorrelationModel y) { if (x.Correlation > y.Correlation) return -1; if (x.Correlation == y.Correlation) return 0; return 1; }
public int Compare(CorrelationModel x, CorrelationModel y) { if (x.Correlation > y.Correlation) { return(-1); } if (x.Correlation == y.Correlation) { return(0); } return(1); }
public int Compare(object x, object y) { CorrelationModel correlationModelX = x as CorrelationModel; CorrelationModel correlationModelY = y as CorrelationModel; if (correlationModelX != null && correlationModelY != null) { return(Compare(correlationModelX, correlationModelY)); } else { return(0); } }
public void GeneratePerformanceQueries() { if (MaxDimensions < MinDimensions) { Debug.WriteLine("Max Dimensions must be >= Min Dimensions!"); return; } //Open DBConnection --> Otherwise first query is slower as usual, because DBConnection is not open SQLCommon parser = new SQLCommon(); DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, "SELECT cars.id FROM cars SKYLINE OF cars.price LOW"); //Use the correct line, depending on how incomparable items should be compared ArrayList listPreferences = new ArrayList(); SqlConnection cnnSQL = new SqlConnection(Helper.ConnectionString); //for CLR performance tets ArrayList preferencesMode = new ArrayList(); if (UseCLR) { cnnSQL.Open(); } switch (Set) { case PreferenceSet.ArchiveComparable: preferencesMode = GetArchiveComparablePreferences(); break; case PreferenceSet.ArchiveIncomparable: preferencesMode = GetArchiveIncomparablePreferences(); break; case PreferenceSet.Jon: preferencesMode = GetJonsPreferences(); break; case PreferenceSet.Mya: preferencesMode = GetMyasPreferences(); break; case PreferenceSet.Barra: preferencesMode = GetBarrasPreferences(); break; case PreferenceSet.All: preferencesMode = GetAllPreferences(); break; case PreferenceSet.Numeric: preferencesMode = GetNumericPreferences(); break; case PreferenceSet.NumericIncomparable: preferencesMode = GetNumericIncomparablePreferences(); break; case PreferenceSet.Categoric: preferencesMode = GetCategoricalPreferences(); break; case PreferenceSet.CategoricIncomparable: preferencesMode = GetCategoricalIncomparablePreferences(); break; case PreferenceSet.MinCardinality: preferencesMode = GetMinimalCardinalityPreferences(); break; case PreferenceSet.LowCardinality: preferencesMode = GetLowCardinalityPreferences(); break; case PreferenceSet.HighCardinality: preferencesMode = GetHighCardinalityPreferences(); break; case PreferenceSet.LowAndHighCardinality: preferencesMode = GetLowAndHighCardinalityPreferences(); break; case PreferenceSet.ForRandom10: preferencesMode = Get10ForRandomPreferences(); break; case PreferenceSet.ForRandom17: preferencesMode = Get17ForRandomPreferences(); break; } //Calculate correlationmatrix and cardinality from the preferences ArrayList correlationMatrix = GetCorrelationMatrix(preferencesMode); ArrayList listCardinality = GetCardinalityOfPreferences(preferencesMode); //Depending on the mode create the sets from the preferences if (Mode == PreferenceChooseMode.Combination) { //Tests every possible combination with y preferences from the whole set of preferences if (MaxDimensions > preferencesMode.Count) { Debug.WriteLine("Combination with more dimensions than preferences. Please reduce dimensions!"); return; } //create all possible combinations and add it to listPreferences for (int i = MinDimensions; i <= MaxDimensions; i++) { GetCombinations(preferencesMode, i, 0, new ArrayList(), ref listPreferences); } } else if (Mode == PreferenceChooseMode.SameOrder) { int draws = MaxDimensions - MinDimensions + 1; //Tests x times randomly y preferences for (int iChoose = 0; iChoose < draws; iChoose++) { ArrayList preferencesRandom = new ArrayList(); ArrayList preferencesChoose = (ArrayList)preferencesMode.Clone(); //First define define randomly how many dimensions int sampleDimensions = iChoose + MinDimensions; //Choose x preferences randomly for (int i = 0; i < sampleDimensions; i++) { preferencesRandom.Add(preferencesChoose[i]); } //add random preferences to listPreferences listPreferences.Add(preferencesRandom); } } else if (Mode == PreferenceChooseMode.Shuffle) { //Tests x times randomly y preferences for (int iChoose = 0; iChoose < _randomDraws; iChoose++) { ArrayList preferencesRandom = new ArrayList(); ArrayList preferencesChoose = (ArrayList)preferencesMode.Clone(); //First define define randomly how many dimensions int differentDimensions = MaxDimensions - MinDimensions + 1; int sampleDimensions = Rnd.Next(differentDimensions) + MinDimensions; //Choose x preferences randomly for (int i = 0; i < sampleDimensions; i++) { int r = Rnd.Next(preferencesChoose.Count); preferencesRandom.Add(preferencesChoose[r]); preferencesChoose.RemoveAt(r); } //add random preferences to listPreferences listPreferences.Add(preferencesRandom); } } else if (Mode == PreferenceChooseMode.Correlation) { if (MaxDimensions > 2) { Debug.WriteLine("This test mode only works for 2 dimensions!"); return; } //Sort correlations to find the strongest correlationMatrix.Sort(new CorrelationModel()); //Sort correlations ascending CorrelationModel model = (CorrelationModel)correlationMatrix[0]; preferencesMode.Clear(); preferencesMode.Add(model.ColA); preferencesMode.Add(model.ColB); listPreferences.Add(preferencesMode); } else if (Mode == PreferenceChooseMode.AntiCorrelation) { if (MaxDimensions > 2) { Debug.WriteLine("This test mode only works for 2 dimensions!"); return; } //Sort correlations ascending correlationMatrix.Sort(new CorrelationModel()); //Take only the two preferences with the worst correlation CorrelationModel model = (CorrelationModel)correlationMatrix[correlationMatrix.Count - 1]; preferencesMode.Clear(); preferencesMode.Add(model.ColA); preferencesMode.Add(model.ColB); listPreferences.Add(preferencesMode); } else if (Mode == PreferenceChooseMode.Independent) { if (MaxDimensions > 2) { Debug.WriteLine("This test mode only works for 2 dimensions!"); return; } //Sort correlations to find the strongest correlationMatrix.Sort(new CorrelationModel()); //Find the most independent atributes (closest to zero) CorrelationModel modelBefore = new CorrelationModel(); CorrelationModel modelAfter = new CorrelationModel(); for (int i = 0; i <= correlationMatrix.Count; i++) { CorrelationModel model = (CorrelationModel)correlationMatrix[i]; if (model.Correlation > 0) { //continue until the correlation turnaround modelBefore = model; } else { modelAfter = model; //Leave the function, because now the correlation is getting worse break; } } //Add the two preferences to the list, that are closer to zero preferencesMode.Clear(); if (Math.Abs(modelBefore.Correlation) > Math.Abs(modelAfter.Correlation)) { preferencesMode.Add(modelAfter.ColA); preferencesMode.Add(modelAfter.ColB); } else { preferencesMode.Add(modelBefore.ColA); preferencesMode.Add(modelBefore.ColB); } listPreferences.Add(preferencesMode); } List<SkylineStrategy> listStrategy = new List<SkylineStrategy>(); if (Strategy == null) { //If no strategy is defined --> Take all possible algorithms //listStrategy.Add(new SkylineSQL()); listStrategy.Add(new SkylineBNLSort()); listStrategy.Add(new SkylineBNLSort()); listStrategy.Add(new SkylineDQ()); listStrategy.Add(new SkylineHexagon()); listStrategy.Add(new SkylineDecisionTree()); } else { listStrategy.Add(Strategy); } //Generates the R-Commands for the rpref package (for testig exactly the same statements in rpref) if (WriteRCommand) { StringBuilder sb = new StringBuilder(); sb.AppendLine("---------------------------------------------"); sb.AppendLine("-----------------R-Commands------------------"); sb.AppendLine("---------------------------------------------"); sb.AppendLine("library(rPref)"); sb.AppendLine("setwd(\"E:\\\\Projekte\\\\prefSQL_Paper\\\\Test Scalagon\")"); sb.AppendLine("mydata = read.csv(\"cars_all.csv\", sep=\";\")"); sb.AppendLine("skylinesize <- array(" + listPreferences.Count + ":1)"); sb.AppendLine("timeins <- array(" + listPreferences.Count + ":1)"); for (int iPreferenceIndex = 0; iPreferenceIndex < listPreferences.Count; iPreferenceIndex++) { ArrayList preferences = (ArrayList)listPreferences[iPreferenceIndex]; ArrayList subPreferences = preferences; //.GetRange(0, i); sb.Append("time = system.time(sky1 <- psel(mydata, "); foreach(String pref in preferences) { String rCommand = ""; if(pref.IndexOf("cars.") == -1) { //Categorical preferences //String tableName = pref.Substring(0, pref.IndexOf(".")); rCommand = "low(" + pref.Substring(0, pref.IndexOf(" (")).Replace(".name", "") + ")"; /*preferences.Add("colors.name ('red' >> 'blue' >> 'green' >> 'gold' >> 'black' >> 'gray' >> 'bordeaux' >> OTHERS EQUAL)"); preferences.Add("bodies.name ('bus' >> 'cabriolet' >> 'limousine' >> 'coupé' >> 'van' >> 'estate car' >> OTHERS EQUAL)"); preferences.Add("fuels.name ('petrol' >> 'diesel' >> 'bioethanol' >> 'electro' >> 'gas' >> 'hybrid' >> OTHERS EQUAL)"); preferences.Add("makes.name ('BENTLEY' >> 'DAIMLER' >> 'FIAT'>> 'FORD' >> OTHERS EQUAL)"); preferences.Add("conditions.name ('new' >> 'occasion' >> 'demonstration car' >> 'oldtimer' >> OTHERS EQUAL)"); preferences.Add("drives.name ('front wheel' >> 'all wheel' >> 'rear wheel' >> OTHERS EQUAL)"); preferences.Add("transmissions.name ('manual' >> 'automatic' >> OTHERS EQUAL)");*/ } else { //Numeric preference if (pref.IndexOf("LOW") > 0) { //LOW preference rCommand = "low(" + pref.Substring(0, pref.IndexOf(" ")).Replace("cars.", "") +")"; } else { //HIGH preferences rCommand = "high(" + pref.Substring(0, pref.IndexOf(" ")).Replace("cars.", "") + ")"; } } sb.Append(rCommand); //Don't add * on last record if (pref != (string)preferences[preferences.Count-1]) { sb.Append(" * "); } } sb.AppendLine("))"); sb.AppendLine("timeins[" + (iPreferenceIndex + 1) + "] = time[3]"); sb.AppendLine("skylinesize[" + (iPreferenceIndex + 1) + "] = nrow(sky1)"); sb.AppendLine("skylinesize[" + (iPreferenceIndex + 1) + "]"); sb.AppendLine("timeins[" + (iPreferenceIndex + 1) + "]"); //string strSkylineOf = "SKYLINE OF " + string.Join(",", (string[])subPreferences.ToArray(Type.GetType("System.String"))); //sb.AppendLine(strSkylineOf); } sb.AppendLine("mean(skylinesize)"); sb.AppendLine("min(skylinesize)"); sb.AppendLine("max(skylinesize)"); sb.AppendLine("var(skylinesize)"); sb.AppendLine("sd(skylinesize)"); sb.AppendLine("mean(timeins)"); sb.AppendLine("min(timeins)"); sb.AppendLine("max(timeins)"); sb.AppendLine("var(timeins)"); sb.AppendLine("sd(timeins)"); sb.AppendLine("---------------------------------------------"); sb.AppendLine("---------------------------------------------"); sb.AppendLine("---------------------------------------------"); sb.AppendLine(""); sb.AppendLine(""); Debug.Write(sb.ToString()); //create filename string strFileName = Path + "AllShuffle_RCommands" + DateTime.Now.Ticks + ".txt"; StreamWriter outfile = new StreamWriter(strFileName); outfile.Write(sb.ToString()); outfile.Close(); } int iStrategy = 0; foreach(SkylineStrategy currentStrategy in listStrategy) { //Take all strategies //BNL with attributeposition sorting and bnl with entropy function sorting //For testing the BNL automatically (shuffle mode), once with attributeposition and once with entropy function if (iStrategy == 1) { WindowSort = SQLCommon.Ordering.AttributePosition; } iStrategy++; StringBuilder sb = new StringBuilder(); string strSeparatorLine; if (Sampling) { strSeparatorLine = PerformanceSampling.GetSeparatorLine(ExcessiveTests); } else { strSeparatorLine = FormatLineString('-', "", "", "", "", "", "", "", "", "", "", ""); } if (GenerateScript == false) { //Header sb.AppendLine(" Path: " + Path); sb.AppendLine(" Algorithm: " + currentStrategy); sb.AppendLine(" Use CLR: " + UseCLR); sb.AppendLine(" Preference Set: " + Set.ToString()); sb.AppendLine(" Preference Mode: " + Mode.ToString()); sb.AppendLine(" Host: " + Environment.MachineName); sb.AppendLine(" Set of Preferences: " + listPreferences.Count); sb.AppendLine(" Trials: " + Trials); sb.AppendLine(" Table size: " + TableSize.ToString()); sb.AppendLine(" Dimension from: " + MinDimensions.ToString()); sb.AppendLine(" Dimension to: " + MaxDimensions.ToString()); sb.AppendLine(" BNL Initial Sort: " + WindowSort.ToString()); sb.AppendLine(" BNL Window Handling: " + WindowHandling.ToString()); //sb.AppendLine("Correlation Coefficients:" + string.Join(",", (string[])preferences.ToArray(Type.GetType("System.String")))); //sb.AppendLine(" Cardinalities:" + string.Join(",", (string[])preferences.ToArray(Type.GetType("System.String")))); if (Sampling) { sb.AppendLine(" Sampling: true"); sb.AppendLine(" Subsets Count: " + SamplingSubsetsCount); sb.AppendLine(" Subset Dimension: " + SamplingSubsetDimension); sb.AppendLine(" Sampling Runs: " + SamplingSamplesCount); } sb.AppendLine(""); if (Sampling) { sb.AppendLine(PerformanceSampling.GetHeaderLine()); } else { sb.AppendLine(FormatLineString(' ', "preference set", "trial", "dimensions", "skyline size", "time total", "time algorithm", "min correlation", "max correlation", "product cardinality", "number of moves", "number of comparisons")); } sb.AppendLine(strSeparatorLine); Debug.Write(sb); } List<long> reportDimensions = new List<long>(); List<long> reportSkylineSize = new List<long>(); List<long> reportTimeTotal = new List<long>(); List<long> reportTimeAlgorithm = new List<long>(); List<long> reportNumberOfMoves = new List<long>(); List<long> reportNumberOfComparisons = new List<long>(); List<double> reportMinCorrelation = new List<double>(); List<double> reportMaxCorrelation = new List<double>(); List<double> reportCardinality = new List<double>(); var perfSampling = new PerformanceSampling(SamplingSubsetsCount, SamplingSubsetDimension, SamplingSamplesCount, ExcessiveTests); //For each preference set in the preference list for (int iPreferenceIndex = 0; iPreferenceIndex < listPreferences.Count; iPreferenceIndex++) { ArrayList preferences = (ArrayList)listPreferences[iPreferenceIndex]; //Go only down two 3 dimension (because there are special algorithms for 1 and 2 dimensional skyline) //for (int i = MinDimensions; i <= preferences.Count; i++) //{ //ADD Preferences to SKYLINE ArrayList subPreferences = preferences; //.GetRange(0, i); string strSkylineOf = "SKYLINE OF " + string.Join(",", (string[])subPreferences.ToArray(Type.GetType("System.String"))); //SELECT FROM string strSQL = "SELECT cars.id FROM "; if (TableSize == Size.Small) { strSQL += "cars_small"; } else if (TableSize == Size.Medium) { strSQL += "cars_medium"; } else if (TableSize == Size.Large) { strSQL += "cars_large"; } if (!UseNormalizedValues) { strSQL += " cars "; } else { strSQL += "cars_normalized cars "; } //Add Joins strSQL += GetJoinsForPreferences(strSkylineOf); //Add Skyline-Clause strSQL += strSkylineOf; //Convert to real SQL parser = new SQLCommon(); parser.SkylineType = currentStrategy; parser.ShowInternalAttributes = true; parser.WindowHandling = WindowHandling; parser.WindowSort = WindowSort; if (GenerateScript == false) { for (int iTrial = 0; iTrial < Trials; iTrial++) { Stopwatch sw = new Stopwatch(); try { double minCorrelation = 0; double maxCorrelation = 0; SearchCorrelation(subPreferences, correlationMatrix, ref minCorrelation, ref maxCorrelation); double cardinality = SearchCardinality(subPreferences, listCardinality); long timeAlgorithm = 0; long skylineSize = 0; long numberOfMoves = 0; long numberOfComparisons = 0; if (Sampling) { string strTrial = iTrial + 1 + " / " + _trials; string strPreferenceSet = iPreferenceIndex + 1 + " / " + listPreferences.Count; Console.WriteLine(strPreferenceSet); string strLine = perfSampling.MeasurePerformance(iTrial, iPreferenceIndex, listPreferences, preferences, parser, sw, reportDimensions, reportSkylineSize, reportTimeTotal, reportTimeAlgorithm, reportMinCorrelation, reportMaxCorrelation, minCorrelation, maxCorrelation, reportCardinality, cardinality, strSQL, strPreferenceSet, strTrial, reportNumberOfMoves, numberOfMoves, reportNumberOfComparisons, numberOfComparisons); Debug.WriteLine(strLine); sb.AppendLine(strLine); } else { sw.Start(); ArrayList clauseID = new ArrayList(); String strIDs = ""; for (int skylineLevel = 1; skylineLevel <= SkylineUpToLevel; skylineLevel++) { if (UseCLR) { string strSP = parser.ParsePreferenceSQL(strSQL); SqlDataAdapter dap = new SqlDataAdapter(strSP, cnnSQL); dt.Clear(); //clear datatable dap.Fill(dt); } else { parser.Cardinality = (long)cardinality; string strSQLWithWHERE = strSQL; strSQLWithWHERE = strSQL.Substring(0, strSQL.IndexOf("SKYLINE OF")); strSQLWithWHERE += strIDs; strSQLWithWHERE += strSQL.Substring(strSQL.IndexOf("SKYLINE OF")); dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, strSQLWithWHERE); } timeAlgorithm += parser.TimeInMilliseconds; numberOfMoves += parser.NumberOfMoves; numberOfComparisons += parser.NumberOfComparisons; skylineSize += dt.Rows.Count; //only if more queries are requested if (skylineLevel < SkylineUpToLevel && currentStrategy.GetType() != typeof(prefSQL.SQLSkyline.MultipleSkylineBNL)) { //Add ids to WHERE clause foreach (DataRow row in dt.Rows) { clauseID.Add((int)row[0]); } //Add WHERE clause with IDs that were already in the skyline strIDs = ""; foreach (int id in clauseID) { strIDs += id + ","; } if (strIDs.Length > 0) { strIDs = "WHERE cars.id NOT IN (" + strIDs.TrimEnd(',') + ") "; } } else { skylineLevel = SkylineUpToLevel; } } sw.Stop(); reportDimensions.Add(preferences.Count); reportSkylineSize.Add(skylineSize); reportTimeTotal.Add(sw.ElapsedMilliseconds); reportTimeAlgorithm.Add(timeAlgorithm); reportNumberOfMoves.Add(numberOfMoves); reportNumberOfComparisons.Add(numberOfComparisons); reportMinCorrelation.Add(minCorrelation); reportMaxCorrelation.Add(maxCorrelation); reportCardinality.Add(cardinality); //trial|dimensions|skyline size|time total|time algorithm string strTrial = iTrial + 1 + " / " + _trials; string strPreferenceSet = iPreferenceIndex + 1 + " / " + listPreferences.Count; Console.WriteLine(strPreferenceSet); string strLine = ""; //Was there an error? if (dt.Rows.Count == 0) { strLine = FormatLineString("Error! " + strPreferenceSet, strTrial, preferences.Count, dt.Rows.Count, sw.ElapsedMilliseconds, timeAlgorithm, minCorrelation, maxCorrelation, cardinality, numberOfMoves, numberOfComparisons); } else { strLine = FormatLineString(strPreferenceSet, strTrial, preferences.Count, dt.Rows.Count, sw.ElapsedMilliseconds, timeAlgorithm, minCorrelation, maxCorrelation, cardinality, numberOfMoves, numberOfComparisons); } Debug.WriteLine(strLine); sb.AppendLine(strLine); } } catch (Exception e) { Debug.WriteLine(e.Message); return; } } } else { strSQL = parser.ParsePreferenceSQL(strSQL); string[] sizes = { "small", "medium", "large", "superlarge" }; //Format for each of the customer profiles sb.AppendLine("PRINT '----- -------------------------------------------------------- ------'"); sb.AppendLine("PRINT '----- " + (preferences.Count + 1) + " dimensions ------'"); sb.AppendLine("PRINT '----- -------------------------------------------------------- ------'"); foreach (string size in sizes) { sb.AppendLine("GO"); //we need this in order the profiler shows each query in a new line sb.AppendLine(strSQL.Replace("cars", "cars_" + size)); } } //} } //////////////////////////////// //Summary /////////////////////////////// if (GenerateScript == false) { if (Sampling) { perfSampling.AddSummary(sb, strSeparatorLine, reportDimensions, reportSkylineSize, reportTimeTotal, reportTimeAlgorithm, reportMinCorrelation, reportMaxCorrelation, reportCardinality, reportNumberOfMoves, reportNumberOfComparisons); perfSampling.AddPreferenceSetInformation(sb, listPreferences, strSeparatorLine); } else { AddSummary(sb, strSeparatorLine, reportDimensions, reportSkylineSize, reportTimeTotal, reportTimeAlgorithm, reportMinCorrelation, reportMaxCorrelation, reportCardinality, reportNumberOfMoves, reportNumberOfComparisons); } } //Write some empty lines (clarification in output window) Debug.WriteLine(""); Debug.WriteLine(""); Debug.WriteLine(""); //Write in file string strFiletype; if (GenerateScript == false) { strFiletype = ".txt"; } else { strFiletype = ".sql"; } //create filename string strFileName = Path + Set.ToString() + "_" + Mode.ToString() + "_" + MinDimensions + "_" + MaxDimensions + "_" + currentStrategy + DateTime.Now.Ticks + strFiletype; StreamWriter outfile = new StreamWriter(strFileName); outfile.Write(sb.ToString()); outfile.Close(); } //close connection if (UseCLR) { cnnSQL.Close(); } }
private ArrayList GetCorrelationMatrix(ArrayList preferences) { DataTable dt = GetSQLFromPreferences(preferences, false); double[] colA = new double[dt.Rows.Count]; double[] colB = new double[dt.Rows.Count]; //Calculate correlation between the attributes ArrayList listCorrelation = new ArrayList(); for (int iIndex = 0; iIndex < preferences.Count; iIndex++) { for (int iPref = 0; iPref <= iIndex; iPref++) { //Don't compare same preferences (correlation is always 1) if (iIndex != iPref) { for (int i = 0; i < dt.Rows.Count; i++) { colA[i] = (int)dt.Rows[i][iIndex]; colB[i] = (int)dt.Rows[i][iPref]; } double correlation = MyMathematic.GetPearson(colA, colB); CorrelationModel model = new CorrelationModel(preferences[iIndex].ToString(), preferences[iPref].ToString(), correlation); listCorrelation.Add(model); } } } return listCorrelation; }